Sebastian Sebastian - 2 months ago 7
SQL Question

case when with group by in SQL

Very simple task but I fail to write the corresponding sql.

Two factor variables, var1 with 5 levels, var2 is binary with 0/1.

I want to count all rows grouped by var1 and var2, but with new variables.

sel
var1,
var2,
count(*)
from tab
group by var1,var2;


gives me

var1 var2 Count(*)
1 1 0 32
2 1 1 80
3 2 1 80
4 2 0 33
5 3 1 82
6 3 0 33
7 4 1 81
8 4 0 33
9 5 0 33
10 5 1 88


Desired is this format:

var1 var1=0 var1=1
1 32 80
2 33 80
3 33 82
4 33 81
5 33 88


Does not work:

sel
var1,
case
when var2 = 1 then count(*)
end as svar1,
case
when var2 = 0 then count(*)
end as svar2
from tab
group by var1;


My querry does not work, because var2 is not part of the associated group,
how do I get this format?

Answer

You need to do coniditional aggregation..please provide some test data to test as well

select var1
sum(case when var1=0 then 1 else 0 end) as var1,
sum(case when var1=1 then 1 else 0 end) as var2
group by var1
Comments