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.

Possible duplicate of [How to get multiple counts with one SQL query?](https://stackoverflow.com/questions/12789396/how-to-get-multiple-counts-with-one-sql-query) — Tab Alleman, Jan 08 '19 at 15:39

John Woo · Accepted Answer · 2013-02-06T23:48:28.477

120

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

edited Feb 06 '13 at 23:48

answered Feb 06 '13 at 23:41

John Woo

258,903
69
498
492

1

Thanks for your reply JW. I have tried it out but I am getting the following error, which is similar to the issues I was having myself: Error: Column 'status' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. (State:37000, Native Code: 1FB8) Error: Column 'status' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. (State:37000, Native Code: 1FB8) – jj2 Feb 06 '13 at 23:50
I believe the `ELSE 0` is redundant as it will be `NULL` otherwise, at least in psql – CervEd May 17 '21 at 20:27
@CervEd That's correct (at least in PostgreSQL): `THEN 1 END` works fine. – Nate Barbettini Aug 21 '21 at 23:49

SQL: Multiple count statements with different criteria

1 Answers1

Linked