Count statement with Join and where clause

Question

enter image description here

The top two are individual results.. the third on is the statement in which i am trying to join another table in it.My problem is i am not able to set the conditions in the sql statement. In the last join statement i got two columns with Route Id and the sum with Flab=1 but am not able to set the condition in which the flag =0 ..please help I need T2 as the last column.

Ed Harper · Accepted Answer · 2012-02-26T09:29:16.657

SELECT a.RouteCode, 
       SUM(CASE WHEN b.ScheduledFlag = '1' THEN 1 ELSE 0 END) AS T1,
       SUM(CASE WHEN b.ScheduledFlag = '0' THEN 1 ELSE 0 END) AS T2
FROM Routes a inner join CustomerVisits as b on a.RouteCode = b.RouteCode
WHERE b.RouteStartDate = '12/15/2011' 
and a.DepotCode = '6'
group by a.RouteCode

Note that your date format string is potentially ambiguous depending on the locale of you database connection. Use a locale-safe date format like ODBC canonical (yyyy-mm-dd hh:mi:ss)

score 1 · Answer 2 · answered Feb 26 '12 at 08:38

One way to do this would be to create each count query separately, then do a left join from the routes table on each so that your results aren't filtered if there isn't a match in both count tables.

SELECT q1.routecode,
       t1,
       t2
FROM   routes
       LEFT JOIN (SELECT routecode,
                         COUNT(routecode) AS t1
                  FROM   customervisits
                  WHERE  ( routecode IN ( '701', '702', '704', '703', '705' ) )
                         AND routestartdate = '12/15/2011'
                         AND schelduledflag = '1'
                  GROUP  BY routecode) AS q1
         ON routes.routecode = q1.routecode
       LEFT JOIN (SELECT routecode,
                         COUNT(routecode) AS t2
                  FROM   customervisits
                  WHERE  ( routecode IN ( '701', '702', '704', '703', '705' ) )
                         AND routestartdate = '12/15/2011'
                         AND schelduledflag = '0'
                  GROUP  BY routecode) AS q2
         ON routes.routecode = q2.routecode
WHERE  a.depotcode = '6'  and (t1 is not null or t2 is not null);

the reason i am going through all this trouble is i dont have to write this script + i am joing the two table because i am not sure about the route code and hence bringing them from Routes table ..so not sure your script is the best way to go...the must be an easier way ..but thx for the effort :) — Fhd.ashraf, Feb 26 '12 at 08:44
Oh, I missed the point of the join...seems obvious now. And yes, it's messy. I think Ed Harper's does what you want and is much simpler. — gangreen, Feb 26 '12 at 08:48

Count statement with Join and where clause

2 Answers2