如何统计筛选结果集中各type值的行数，无匹配时显示0

原始数据 #

问题描述 #

需要统计created_at在2022-10-01至2022-10-02区间内，每个type的行数，即使该type在筛选结果中没有匹配记录，也要显示0。

原查询语句：

SELECT type, COUNT(*)
FROM tablename
WHERE created_at between '2022-10-01' and '2022-10-02'
GROUP BY type;

返回结果：

期望结果：

解决方案 #

原查询的问题在于WHERE子句直接过滤掉了teacher类型的记录，导致分组时无法包含该类型。要实现需求，需要先获取所有唯一的type值，再左关联筛选后的统计结果：

-- 先获取所有distinct的type，再左连接统计结果
SELECT t.type, COALESCE(c.count_num, 0) AS count
FROM (SELECT DISTINCT type FROM tablename) t
LEFT JOIN (
    SELECT type, COUNT(*) AS count_num
    FROM tablename
    WHERE created_at BETWEEN '2022-10-01' AND '2022-10-02'
    GROUP BY type
) c ON t.type = c.type;

说明 #

1. 子查询t提取表中所有不重复的type值，确保所有类型都被纳入统计范围；
2. 子查询c执行原有的时间筛选和分组统计，得到符合条件的各类型行数；
3. 通过LEFT JOIN关联两个子查询，保证t中的所有type都被保留；
4. 使用COALESCE函数将无匹配的NULL值转换为0，满足显示0值的需求。

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