Quiero combinar tres consultas de recuento y obtener resultados en columnas separadas. Así es como se ve mi conjunto de datos.

enter image description here

He probado esto

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;

Esta es mi salida

enter image description here

Pero la salida esperada es algo como esto

enter image description here

-1
Ada S 19 oct. 2019 a las 18:37

1 respuesta

La mejor respuesta

Simplemente use la agregación condicional. . . varias veces:

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;
0
Gordon Linoff 19 oct. 2019 a las 15:38