如何在COUNT聚合中包含0(int)结果?PostgreSQL

Question

INSERT INTO Luisteraar(nummerId, oordeel) VALUES
(4,'good'),
(4,'not-rated'),
(5,'good'),
(5,'good'),
(6,'bad'),
(6,'good'),
(6,'good'),
(7,'not-rated'),
(7,'not-rated'),
(8,'bad'),
(8,'good')

所以如果我这样做：

SELECT l.nummerid, count(l.oordeel) as manyGood 
FROM luisteraar l 
WHERE l.oordeel = 'good'
GROUP BY l.nummerid

输出：

nummerid  manyGood
4            1
5            2
6            2
8            1 

问题：我还想要输出manyGood=0的nummerid7。我如何在PostgreSQL 中做到这一点

Answer 1

您可以使用条件聚合而不是WHERE子句：

SELECT l.nummerid,
count(l.oordeel) FILTER (WHERE l.oordeel = 'good') as manyGood 
FROM luisteraar l 
GROUP BY l.nummerid

Answer 2

针对同一表的子查询可以工作，但可能有更好的方法

SELECT nummerid, 
(SELECT count(*) FROM luisteraar WHERE nummerid= l.nummerid AND oordeel= 'leuk') as manyGood 
FROM luisteraar l 
GROUP BY nummerid