Counting same column multiple times, already grouped

Question

I'm trying to count all the "Y"s and "N"s in one column but group them by another column. I can get it to pull just the "Y" or the "N" with a WHERE:

SELECT TABLE2.GROUP_BY_THIS,COUNT(TABLE1.FLAG)
FROM TABLE1 INNER JOIN
TABLE2 On TABLE1.Primary_Key = TABLE2.Foreign_Key
WHERE TABLE1.FLAG_Required = "Y"
GROUP BY TABLE2.GROUP_BY_THIS

and I can see the "Y"s and "N"s for the whole data set and not grouped by the other column using the first answer here: SQL - Counting a column twice but I'm not too sure how to get my desired outcome which should look a little like this:

GROUP_BY_THIS  Y COUNT  N COUNT
-------------------------------
GROUP1         10       1
GROUP2         10       100
GROUP3         0        10
GROUP4         50       500
GROUP5         1000     0

Do I need a further grouping somehow?

score 4 · Accepted Answer · answered May 09 '13 at 11:48

4

SELECT  TABLE2.GROUP_BY_THIS
,       COUNT(CASE WHEN TABLE1.FLAG = 'Y' THEN 1 END)
,       COUNT(CASE WHEN TABLE1.FLAG = 'N' THEN 1 END)
FROM    TABLE1 
JOIN    TABLE2 
ON      TABLE1.Primary_Key = TABLE2.Foreign_Key
GROUP BY 
        TABLE2.GROUP_BY_THIS

answered May 09 '13 at 11:48

Andomar

232,371
49
380
404

Shouldn't it be SUM() instead of COUNT()? – AdamL May 09 '13 at 11:49
@frikozoid in this case when no `ELSE` was specified and the value for true condition is 1, `COUNT()` and `SUM()` will produce the same result. – John Woo May 09 '13 at 11:54
@Andomar Oh, OK. NULLs don't count :). +1 – AdamL May 09 '13 at 11:56

Counting same column multiple times, already grouped

1 Answers1