SQL Group By - generate multiple aggregate columns from a single column

Question

I would like to group Companies and Date and create graph columns for two separate values ​​(Flag = Y and Flag = N). The input table is as follows:

Company Date Flag ------- ------- ----- 001 201201 Y 001 201201 N 001 201202 N 001 201202 N 001 201202 Y 

The result should look like this:

 Company Date Count_Y Count_N ------- ------ ------- ------- 001 201201 1 1 001 201202 1 2 

How to write a SQL query? Any help is appreciated! Thanks!

Answer 1

You can do this using correlated subqueries, for example:

 SELECT Company, Date, (SELECT COUNT(*) FROM MyTable AS T1 WHERE T1.Flag='Y' AND T1.Company=T2.Company AND T1.Date=T2.Date) AS Count_Y, (SELECT COUNT(*) FROM MyTable AS T1 WHERE T1.Flag='N' AND T1.Company=T2.Company AND T1.Date=T2.Date) AS Count_N FROM MyTable AS T2 GROUP BY Company, Date 

You can also do this more succinctly, but perhaps with (perhaps) less readability using the SUM trick:

 SELECT Company, Date, SUM(CASE WHEN Flag='Y' THEN 1 ELSE 0 END) AS Count_Y, SUM(CASE WHEN Flag='N' THEN 1 ELSE 0 END) AS Count_N, FROM MyTable GROUP BY Company, Date 

In Oracle / PLSQL, the DECODE function can be used to replace CASE for even more concise ones:

 SELECT Company, Date, SUM(DECODE(Flag,'Y',1,0)) AS Count_Y, SUM(DECODE(Flag,'N',1,0)) AS Count_N, FROM MyTable GROUP BY Company, Date 

Answer 2

If you have an identifier / key for this table, you can do it like this:

 SELECT [Company], [Date], [Y] Count_Y, [N] Count_N FROM Company PIVOT (COUNT([ID]) FOR FLAG IN ([Y],[N])) pvt 

Where ID is your Company table identifier.

The script with the code is here

---

If you do not have an identifier / key for the table, and the company, date and flag are the only columns that you have, then you can PIVOT the Flag account, as @ConradFrix did in the comments:

 SELECT [Company], [Date], [Y] Count_Y, [N] Count_N FROM Company PIVOT (COUNT(FLAG) FOR FLAG IN ([Y],[N])) pvt