按天数切片数据表：SQL分组查询结果不符预期求修正

原始数据表 #

期望输出结果 #

错误的SQL语句 #

select col1, 
       CASE WHEN days <=2 then count(days) as "<=2days",
       CASE WHEN days > 2 and days <=5 then count(days) as "<=5days",
       CASE WHEN days > 5 and days <=7 then count(days) as "<=7days"
from tableA group by col1,days

错误结果 #

修正后的SQL语句 #

SELECT 
    col1,
    SUM(CASE WHEN days <= 2 THEN 1 ELSE 0 END) AS "<=2days",
    SUM(CASE WHEN days > 2 AND days <= 5 THEN 1 ELSE 0 END) AS "<=5days",
    SUM(CASE WHEN days > 5 AND days <= 7 THEN 1 ELSE 0 END) AS "<=7days"
FROM tableA
GROUP BY col1;

修正说明 #

• 原SQL错误点：
  1. GROUP BY col1, days会将每个不同的days值单独分组，导致同一col1产生多条结果；
  2. CASE中直接使用count(days)逻辑错误，无法正确统计区间内的记录数。
• 修正逻辑：
  1. 仅按col1分组，保证每个类别只返回一行数据；
  2. 利用SUM()函数配合CASE条件判断，符合区间条件时返回1，否则返回0，累加后得到每个区间的准确计数。

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