SQL分组求和报错：无法对含聚合/子查询的表达式使用SUM

问题原因 #

1. 原查询返回同一部门多条记录：查询从用户表pcs_user出发，每个用户生成一行统计结果，DISTINCT仅能去重完全相同的行，无法合并同一部门下不同用户的统计值。
2. 无法用SUM包裹子查询中的COUNT：SQL语法禁止聚合函数（如SUM）嵌套包含子查询的聚合表达式（如COUNT）。

优化后的SQL方案 #

方案一：直接使用条件聚合（推荐，性能更优） #

DECLARE @DateFrom datetime = '2023-08-01'
DECLARE @DateTo datetime = '2023-09-27'

SELECT 
    u.Department,
    -- 任务总数：符合四个条件的任务数量之和
    SUM(
        CASE 
            WHEN pt.tsk_status = 'COMPLETE' 
                AND DATEADD(DAY, -30, @DateTo) > pt.date_closed 
                AND pt.due_date <= @DateTo 
                AND pt.date_alloc >= @DateFrom 
                AND pt.date_closed <= @DateTo THEN 1
            WHEN pt.tsk_status = 'COMPLETE' 
                AND DATEADD(DAY, -60, @DateTo) > pt.date_closed 
                AND pt.due_date <= @DateTo 
                AND pt.date_alloc >= @DateFrom 
                AND pt.date_closed <= @DateTo THEN 1
            WHEN pt.tsk_status <> 'COMPLETE' 
                AND DATEADD(DAY, 30, @DateTo) >= pt.date_alloc 
                AND pt.due_date <= @DateTo 
                AND pt.date_alloc >= @DateFrom THEN 1
            WHEN pt.tsk_status <> 'COMPLETE' 
                AND DATEADD(DAY, 60, pt.due_date) >= pt.date_alloc 
                AND pt.due_date <= @DateTo 
                AND pt.date_alloc >= @DateFrom THEN 1
            ELSE 0
        END
    ) AS [Total Tasks],
    -- 已完成任务数：符合前两个已完成条件的数量之和
    SUM(
        CASE 
            WHEN pt.tsk_status = 'COMPLETE' 
                AND DATEADD(DAY, -30, @DateTo) > pt.date_closed 
                AND pt.due_date <= @DateTo 
                AND pt.date_alloc >= @DateFrom 
                AND pt.date_closed <= @DateTo THEN 1
            WHEN pt.tsk_status = 'COMPLETE' 
                AND DATEADD(DAY, -60, @DateTo) > pt.date_closed 
                AND pt.due_date <= @DateTo 
                AND pt.date_alloc >= @DateFrom 
                AND pt.date_closed <= @DateTo THEN 1
            ELSE 0
        END
    ) AS [Num Completed],
    -- 逾期任务数：符合后两个未完成条件的数量之和
    SUM(
        CASE 
            WHEN pt.tsk_status <> 'COMPLETE' 
                AND DATEADD(DAY, 30, @DateTo) >= pt.date_alloc 
                AND pt.due_date <= @DateTo 
                AND pt.date_alloc >= @DateFrom THEN 1
            WHEN pt.tsk_status <> 'COMPLETE' 
                AND DATEADD(DAY, 60, pt.due_date) >= pt.date_alloc 
                AND pt.due_date <= @DateTo 
                AND pt.date_alloc >= @DateFrom THEN 1
            ELSE 0
        END
    ) AS [Num Overdue]
FROM pcs_user p
INNER JOIN Users u WITH (NOLOCK) ON u.[Reference Number] = p.obj_id
LEFT JOIN pcs_task pt WITH (NOLOCK) ON p.pcs_user_id = pt.to_resource
WHERE p.user_status = 'Active' 
    AND p.pcs_user_id <> 'ADMIN'
GROUP BY u.Department
ORDER BY u.Department

方案二：先统计用户维度，再汇总部门维度 #

若需保留用户级统计逻辑，可先用CTE计算每个用户的任务数，再按部门汇总：

DECLARE @DateFrom datetime = '2023-08-01'
DECLARE @DateTo datetime = '2023-09-27'

WITH UserTaskStats AS (
    SELECT 
        u.Department,
        -- 单个用户的任务总数
        (CASE WHEN pt.tsk_status = 'COMPLETE' 
                AND DATEADD(DAY, -30, @DateTo) > pt.date_closed 
                AND pt.due_date <= @DateTo 
                AND pt.date_alloc >= @DateFrom 
                AND pt.date_closed <= @DateTo THEN 1 ELSE 0 END) +
        (CASE WHEN pt.tsk_status = 'COMPLETE' 
                AND DATEADD(DAY, -60, @DateTo) > pt.date_closed 
                AND pt.due_date <= @DateTo 
                AND pt.date_alloc >= @DateFrom 
                AND pt.date_closed <= @DateTo THEN 1 ELSE 0 END) +
        (CASE WHEN pt.tsk_status <> 'COMPLETE' 
                AND DATEADD(DAY, 30, @DateTo) >= pt.date_alloc 
                AND pt.due_date <= @DateTo 
                AND pt.date_alloc >= @DateFrom THEN 1 ELSE 0 END) +
        (CASE WHEN pt.tsk_status <> 'COMPLETE' 
                AND DATEADD(DAY, 60, pt.due_date) >= pt.date_alloc 
                AND pt.due_date <= @DateTo 
                AND pt.date_alloc >= @DateFrom THEN 1 ELSE 0 END) AS UserTotalTasks,
        -- 单个用户的已完成任务数
        (CASE WHEN pt.tsk_status = 'COMPLETE' 
                AND DATEADD(DAY, -30, @DateTo) > pt.date_closed 
                AND pt.due_date <= @DateTo 
                AND pt.date_alloc >= @DateFrom 
                AND pt.date_closed <= @DateTo THEN 1 ELSE 0 END) +
        (CASE WHEN pt.tsk_status = 'COMPLETE' 
                AND DATEADD(DAY, -60, @DateTo) > pt.date_closed 
                AND pt.due_date <= @DateTo 
                AND pt.date_alloc >= @DateFrom 
                AND pt.date_closed <= @DateTo THEN 1 ELSE 0 END) AS UserCompleted,
        -- 单个用户的逾期任务数
        (CASE WHEN pt.tsk_status <> 'COMPLETE' 
                AND DATEADD(DAY, 30, @DateTo) >= pt.date_alloc 
                AND pt.due_date <= @DateTo 
                AND pt.date_alloc >= @DateFrom THEN 1 ELSE 0 END) +
        (CASE WHEN pt.tsk_status <> 'COMPLETE' 
                AND DATEADD(DAY, 60, pt.due_date) >= pt.date_alloc 
                AND pt.due_date <= @DateTo 
                AND pt.date_alloc >= @DateFrom THEN 1 ELSE 0 END) AS UserOverdue
    FROM pcs_user p
    INNER JOIN Users u WITH (NOLOCK) ON u.[Reference Number] = p.obj_id
    LEFT JOIN pcs_task pt WITH (NOLOCK) ON p.pcs_user_id = pt.to_resource
    WHERE p.user_status = 'Active' 
        AND p.pcs_user_id <> 'ADMIN'
)
SELECT 
    Department,
    SUM(UserTotalTasks) AS [Total Tasks],
    SUM(UserCompleted) AS [Num Completed],
    SUM(UserOverdue) AS [Num Overdue]
FROM UserTaskStats
GROUP BY Department
ORDER BY Department

关键优化点 #

1. 减少表访问次数：原查询每个用户需执行6次子查询访问pcs_task表，优化后仅关联一次表，性能大幅提升。
2. 部门级分组聚合：通过GROUP BY u.Department直接汇总部门统计值，解决同一部门多行的问题。
3. 合规语法替代子查询：用CASE语句替代独立子查询，既符合SQL语法规范，又保留原统计逻辑。

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