sql - PSQL 在同一行上按多个条件分组

Question

假设我有一个这样的表:

Letter  Color  
A       Red    
A       Blue    
B       Red          
C       Red
C       Red

我想要实现的是以下输出格式:

Letter   Red   Blue
A        1     1
B        1     0
C        2     0

尝试如下:

SELECT letter, red, blue FROM (
SELECT letter, count(*) AS red from letters where color = 'red'
GROUP BY letter
UNION
SELECT letter, count(*) AS blue from letters where color = 'blue'
GROUP BY letter
) GROUP BY letter

有人可以帮忙吗？请注意，我不能添加任何 tablefunc 或类似的数据库。提前致谢

Answer 1

我认为最简单的方法是条件聚合:

select letter,
       sum(case when color = 'Red' then 1 else 0 end) as red,
       sum(case when color = 'Blue' then 1 else 0 end) as blue
from letters
group by letter
order by letter;