SQL - 计算列中不同值的出现次数

Question

长话短说:

我有这样的 table

nr   sat   col
1    1     dsag
1    2     gds
1    2     gds
2    1     gdsa
2    2     gf
2    3     gdsa

期待这样的结果:

nr  Sat_1   Sat_2   Sat_3
1   1       2       0
2   1       1       1

我想为“SELECT DISTINCT sat”中的每一行添加新列，并按 nr 进行下一组

Answer 1

这可以使用条件聚合来完成:

select nr, 
       count(*) filter (where sat = 1) as sat_1, 
       count(*) filter (where sat = 2) as sat_2, 
       count(*) filter (where sat = 3) as sat_3
from the_table
group by nr
order by nr;

在线示例:http://rextester.com/YVIR75756