Multiple where clauses in one row sql

Question

I want to take the below statement and fuse it into one query.

SELECT COUNT(*) AS count1 WHERE Month='11' AND Flag = 1
SELECT COUNT(*) AS count2 WHERE Month='11' AND Flag = 2
SELECT COUNT(*) AS count1 WHERE Month='12' AND Flag = 1
    SELECT COUNT(*) AS count2 WHERE Month='12' AND Flag = 2

I want this to display as one query with columns count1 and count2 and rows month 11 and month 12.

Is there a syntax for this?

Answer 1

You can combine SUM and CASE to get various counts in one go:

SELECT
    Month,
    SUM(CASE WHEN Flag=1 THEN 1 ELSE 0 END) as count1,
    SUM(CASE WHEN Flag=2 THEN 1 ELSE 0 END) as count2
from
    ...
WHERE Month in ('11','12')
GROUP BY
    Month /* Other columns? */

Answer 2

With two columns only, it can be something like this:

select
   (SELECT COUNT(*) FROM tablename WHERE Month='11' AND Flag = 1) as 'count1'
   (SELECT COUNT(*) FROM tablename WHERE Month='11' AND Flag = 2) as 'count2'

UNION ALL

select
   (SELECT COUNT(*) FROM tablename WHERE Month='12' AND Flag = 1), 
   (SELECT COUNT(*) FROM tablename WHERE Month='12' AND Flag = 2)

Replace tablename with the name of your table.