按日期分组统计总条数与指定STATUS条数的SQL查询方法

原始表结构 #

业务数据表结构与测试数据如下：

ID NAME     DATE     STATUS 
1  FIRST  2021-01-01   1
1  SECOND 2021-01-02   1
1  THIRD  2021-01-01   0
1  FOURTH 2021-01-02   0
1  FIFTH  2021-01-02   1

查询逻辑 #

• 核心逻辑是按DATE字段做分组聚合计算
• 每组总条数直接用聚合函数COUNT(*)统计即可
• 每组STATUS为1的行数采用条件聚合实现：在SUM函数中做条件判断，STATUS为1时返回1、否则返回0，求和结果就是符合要求的行数

通用SQL语法（兼容所有主流关系型数据库） #

SELECT
  COUNT(*) AS CountRows,
  SUM(CASE WHEN STATUS = 1 THEN 1 ELSE 0 END) AS CountStatus,
  DATE AS `Date`
FROM 你的表名
GROUP BY DATE
ORDER BY DATE;

如果是MySQL场景可以简化条件聚合写法：

SELECT
  COUNT(*) AS CountRows,
  SUM(STATUS = 1) AS CountStatus,
  DATE AS `Date`
FROM 你的表名
GROUP BY DATE
ORDER BY DATE;

输出结果 #

执行上述查询后可得到预期结果：

CountRows CountStatus    Date
   2          1       2021-01-01
   3          2       2021-01-02

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