Join three count queries and get results in to three columns

I want to combine three count queries and get results in to separate columns. This is how my data set looks like.

I have tried this

select id, sum(val = 3) as valcount3
from (select id, s1 as val from t1 union all
      select id, s2 from t1 union all
      select id, s3 from t1 union all
      select id, s4 from t1 union all
      select id, s5 from t1
     ) sub1
group by id
union all
select id, sum(val = 2) as valcount2
from (select id, s1 as val from t1 union all
      select id, s2 from t1 union all
      select id, s3 from t1 union all
      select id, s4 from t1 union all
      select id, s5 from t1
     ) sub
group by id
union all
select id, sum(val = 1) as valcount1
from (select id, s1 as val from t1 union all
      select id, s2 from t1 union all
      select id, s3 from t1 union all
      select id, s4 from t1 union all
      select id, s5 from t1
     ) sub
group by id;

This is my output

But expected output is something like this

Answer

Just use conditional aggregation . . . multiple times:

select id, sum(val = 3) as valcount3,
       sum(val = 2) as valcount2,
       sum(val = 1) as valcount1
from (select id, s1 as val from t1 union all
      select id, s2 from t1 union all
      select id, s3 from t1 union all
      select id, s4 from t1 union all
      select id, s5 from t1
     ) sub1
group by id;

Advertisement

Answer