SQL group by multiple categorical columns and count

Question

I have 3 categorical columns with the same value ranges: 0/1 and NULL.

The columns in SQL:

Column1	Column2	Column3
0	1	1
0	1	0
1	NULL	1
NULL	0	1

I want the counts per category like this:

Categories	Cnt_Col1	Cnt_Col2	Cnt_Col3
0	2	1	1
1	1	2	3
NULL	1	1	0

Does anybody know how this is possible in SQL? The following query doesn't give the expected result:

Select count(*), column1, column2, column3 from table group by column1, column2, column3

Gordon Linoff Gordon Linoff · Accepted Answer · 2021-05-25T10:23:03

I think you want to unpivot and then aggregate. In generic SQL, you can use:

select category,
       sum(case when which = 'column1' then 1 else 0 end) as cnt_col1,
       sum(case when which = 'column2' then 1 else 0 end) as cnt_col2,
       sum(case when which = 'column3' then 1 else 0 end) as cnt_col3
from ((select column1 as category, 'column1' as which from t) union all
      (select column2 as category, 'column2' as which from t) union all
      (select column3 as category, 'column3' as which from t) 
     ) c
group by category;

In SQL Server, I recommend APPLY to unpivot:

select v.category,
       sum(case when v.which = 'column1' then 1 else 0 end) as cnt_col1,
       sum(case when v.which = 'column2' then 1 else 0 end) as cnt_col2,
       sum(case when v.which = 'column3' then 1 else 0 end) as cnt_col3
from t cross apply
     (values (column1, 'column1'),
             (column2, 'column2'),
             (column3, 'column3')
     ) v(category, which)
group by v.category;

SQL group by multiple categorical columns and count

1 Answers