如何使用MySQL的count和group by查询包含0计数的分组统计结果

你可以手动构造包含所有预期状态的临时行集，再和原表的统计结果做左连接，即可将不存在的状态的计数补为0，无需额外新建物理表。

通用兼容写法（支持MySQL、PostgreSQL、Hive、Spark SQL等绝大多数SQL引擎） #

SELECT s.status, IFNULL(d.cnt, 0) AS count
FROM (
    -- 手动拼接所有需要统计的状态枚举
    SELECT 'done' AS status
    UNION ALL
    SELECT 'failed' AS status
    UNION ALL
    SELECT 'pending' AS status
) s
LEFT JOIN (
    -- 原表按状态统计的逻辑
    SELECT status, COUNT(*) AS cnt
    FROM test.data
    GROUP BY status
) d ON s.status = d.status;

简化写法（支持VALUES语法的SQL引擎可用，比如PostgreSQL、SQL Server、Spark SQL等） #

SELECT s.status, COALESCE(d.cnt, 0) AS count
FROM (VALUES ('done'), ('failed'), ('pending')) AS s(status)
LEFT JOIN (
    SELECT status, COUNT(*) AS cnt
    FROM test.data
    GROUP BY status
) d ON s.status = d.status;

逻辑说明 #

• 内层通过UNION ALL或者VALUES构造完整的状态枚举列表，不需要依赖任何物理表
• 左连接原表的统计结果，原统计中没有数据的状态对应计数会返回NULL，用IFNULL/COALESCE/NVL（不同SQL方言的空值处理函数，功能一致）将NULL转换为0即可
• 无需额外加WHERE过滤条件，左连接会自动排除你不需要统计的其他status值

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