CODEWITHSUNDEEP
sqljoincount
Fhd.ashraf
Fhd.ashraf

Reputation: 537

Count statement with Join and where clause

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.

Upvotes: 3

Views: 1998

Answers (2)

Ed Harper
Ed Harper

Reputation: 21495

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)

Upvotes: 5

gangreen
gangreen

Reputation: 909

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);

Upvotes: 1

Related Questions