如何统计tasks表中各tasks_status状态记录数（含done单独统计）

1. 统计所有状态的记录数（含done） #

使用GROUP BY按tasks_status分组，结合COUNT()函数可直接获取每种状态的记录数：

SELECT tasks_status, COUNT(id) AS status_count
FROM tasks
GROUP BY tasks_status;

该查询会返回对应四种状态的记录数结果行，若部分状态无数据则不会返回对应行。如果需要强制显示所有四种状态（即使数量为0），可通过子表生成所有状态后左连接实现：

SELECT s.status_name, COUNT(t.id) AS status_count
FROM (
    SELECT 'done' AS status_name UNION ALL
    SELECT 'pending' UNION ALL
    SELECT 'forwarded' UNION ALL
    SELECT 'cancelled'
) s
LEFT JOIN tasks t ON s.status_name = t.tasks_status
GROUP BY s.status_name;

2. 单独统计done状态的总数 #

通过WHERE过滤目标状态，再用COUNT()计算总数：

SELECT COUNT(id) AS done_count
FROM tasks
WHERE tasks_status = 'done';

3. 同查询同时获取done总数与全状态统计 #

若需要在一个结果集中同时展示done的总数量和所有状态的明细统计，可使用条件聚合或子查询：

方式一：条件聚合+ROLLUP #

SELECT
    COUNT(CASE WHEN tasks_status = 'done' THEN 1 END) AS total_done,
    tasks_status,
    COUNT(id) AS status_count
FROM tasks
GROUP BY tasks_status WITH ROLLUP;

WITH ROLLUP会额外生成一行总计数据，每行都会显示done状态的总数量。

方式二：子查询关联 #

SELECT
    (SELECT COUNT(id) FROM tasks WHERE tasks_status = 'done') AS total_done,
    tasks_status,
    COUNT(id) AS status_count
FROM tasks
GROUP BY tasks_status;

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