SQL: Multiple count statements with different criteria

Question

I was just wondering if there is any way to get two separate "count" totals from a table using one query? That is, using a table similar to the following I would like to retrieve each code (distinct) and show the total number of status' NOT equal to X or D, and then have an additional column that shows the total number of status' equal to X or D and the cancel date is greater than a given date (say, the last 14 days).

Table:

Code:  Status  Cancel_Date ----------------------------------- AAA    X       2012-02-01 AAA BBB    X       2012-02-01 AAA    D       2012-01-01 AAA BBB     BBB    D       2012-02-01 BBB    X       2012-01-01 

Example result (based on the above data):

Code:  TotalNotXorD     TotalXorD ------------------------------------ AAA    2                1 BBB    1                2 

TotalNotXorD: e.g.

select code, count(*)  from table  where status not in('X','D')  group by code 

TotalXorD: e.g.

select code, count(*)  from table  where status in('X','D')    and cancel_date >= '2012-02-01'  group by code 

I have looked at doing subqueries etc. but I can't seem to get the results I need.

Any ideas?

Thanks.

Answer 1

SELECT  a.code,         COALESCE(b.totalNotXorD, 0 ) totalNotXorD,         COALESCE(c.totalXorD, 0 ) totalXorD, FROM    (SELECT DISTINCT Code FROM tableName) a         LEFT JOIN         (             select code, count(*) totalNotXorD             from table              where status not in('X','D')              group by code         ) b ON a.code = b.code         LEFT JOIN         (             select code, count(*) totalXorD             from table              where status in('X','D')                and cancel_date >= '2012-02-01'              group by code         ) c ON a.code = c.code 

or simply doing CASE

SELECT  Code,         SUM(CASE WHEN status NOT IN ('X','D') OR status IS NULL THEN 1 ELSE 0 END) TotalNotXorD,         SUM(CASE WHEN status IN ('X','D') AND cancel_date >= '2012-02-01' THEN 1 ELSE 0 END) TotalXorD   FROM    tableName GROUP   BY Code