PostgreSQL 10中GROUP BY如何返回字段所有值（含计数为0项）

要返回kind字段的所有值并统计对应region='eng'的记录数（包括计数为0的情况），同时满足必须使用GROUP BY的要求，推荐用条件聚合的方式实现：

SELECT
    kind,
    COUNT(CASE WHEN region = 'eng' THEN 1 END) AS count
FROM "table"
GROUP BY kind

原理说明 #

• 原查询的WHERE region='eng'会直接过滤掉非eng区域的行，导致无匹配记录的kind值被排除。
• 使用CASE WHEN region='eng' THEN 1 END，仅当区域为eng时返回有效数值，否则返回NULL；而COUNT()函数会忽略NULL值，刚好实现目标统计。
• 由于未提前过滤行，GROUP BY kind会保留表中所有kind值，无匹配记录的kind计数会显示为0。

也可以通过左连接的方式实现：

SELECT
    k.kind,
    COALESCE(c.count, 0) AS count
FROM (SELECT DISTINCT kind FROM "table") k
LEFT JOIN (
    SELECT kind, COUNT(*) AS count
    FROM "table"
    WHERE region = 'eng'
    GROUP BY kind
) c ON k.kind = c.kind
GROUP BY k.kind, c.count

注意事项 #

PostgreSQL中table是关键字，引用表名时需要用双引号包裹："table"。

内容的提问来源于stack exchange，提问作者SadovniK