CODEWITHSUNDEEP
sqlteradata
Sebastian
Sebastian

Reputation: 2570

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.

select
    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:

select
    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 query does not work, because var2 is not part of the associated group, how do I get this format?

Upvotes: 1

Views: 2831

Answers (3)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

you can try by using this query

select var1,0 as [var1=0],1 as [var1=1]
from(
select var1,var2,QTY from

(
select
var1,
var2,
count(*) as QTY
 from tab
group by var1,var2;
 ) )up pivot (sum(QTY) for var1 in(0,1)) as pvt

order by var1

Upvotes: 0

TheGameiswar
TheGameiswar

Reputation: 28900

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

Upvotes: 3

Denny Sutedja
Denny Sutedja

Reputation: 538

try this

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

Upvotes: -1

Related Questions