10

I would like to group by Company & Date and generate count columns for 2 separate values (Flag=Y and Flag=N). Input table looks like this:

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

The output should look like this:

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

How can I write the SQL query? Any kind of help is appreciated! Thanks!

Thracian
  • 651
  • 4
  • 8
  • 24
  • What is the schema for this table? More specifically, Do you have any more columns besides Company, Date and Flag? – Kash Sep 10 '12 at 21:11

2 Answers2

20

You can do it using correlated subqueries like this:

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 it more concisely, but perhaps with (arguably) slighly 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 the CASE for the even more concise:

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
Michael Goldshteyn
  • 71,784
  • 24
  • 131
  • 181
3

If you have an identifier/key for this table, then you can pivot 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 identifier for the table Company.

Fiddle with the code here

If you do not have an identifier/key for the table and Company, Date and Flag are the only columns you have, then you can do a PIVOT on the count of the Flag itself like @ConradFrix has suggested in the comments: 

SELECT 
  [Company], 
  [Date], 
  [Y] Count_Y, 
  [N] Count_N
  FROM Company
PIVOT 
  (COUNT(FLAG) FOR FLAG IN ([Y],[N])) pvt
Kash
  • 8,799
  • 4
  • 29
  • 48
  • The Pivot is meant for grouping. – Kash Sep 10 '12 at 21:05
  • You could just do `COUNT(FLAG) FOR FLAG IN ` [see here](http://sqlfiddle.com/#!3/e5b6b/3) – Conrad Frix Sep 10 '12 at 21:06
  • 1
    @ConradFrix - tried that, but if you have even a single column besides the 3 in the table, then you don't get 2 rows like you need. – Kash Sep 10 '12 at 21:11
  • Thanks a lot for the answer. The source table has many other columns and it does not have an ID field. While using PIVOT is a plausible options, Michael's solution fits my needs better. – Thracian Sep 11 '12 at 15:50
  • If none of the other columns are unique, then you have a badly designed table. If any of the other columns are unique, then you can use that column as the ID column. Using `PIVOT` may seem unnerving at first, but `PIVOT` was introduced specifically to handle situations like yours. You can compare the performance of each and see which one is better. – Kash Sep 11 '12 at 15:54