多条件分组统计T-SQL报错：列未在聚合或GROUP BY子句中

需要按年份和月份分组，对投诉表进行多条件统计：

• 每月每年的总接收投诉数
• 每月每年已解决的投诉数
• 每月每年状态为New的投诉数

使用嵌套SELECT语句分别统计时，单独运行子查询正常，但整体执行出现错误：

Column 'db.CustomerComplaints.id_Contact' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

原代码：

SELECT 
YEAR(ReceivedDate) AS 'Year',
FORMAT(ReceivedDate, 'MMMM') AS 'Month name',
COUNT(*) AS 'Received Complaints'
,
    (SELECT COUNT(*)
        FROM db.CustomerComplaints t
        WHERE t.status = 'Resolved'
        AND t.id_Contact = cc.id_Contact
    ) AS 'Resolved Complaints'
,
    (
        SELECT COUNT(*)
        FROM db.CustomerComplaints t
        WHERE t.status = 'New'
        AND t.id_Contact = cc.id_Contact
    ) AS 'New Complaints'
FROM db.CustomerComplaints cc
LEFT JOIN db.ReferralUpdates r
ON cc.id_Contact = r.Reference
WHERE r.ReferenceCode = 'Project1'
GROUP BY YEAR(ReceivedDate), FORMAT(ReceivedDate, 'MMMM')

期望结果：

错误原因 #

原代码的子查询通过t.id_Contact = cc.id_Contact关联外层表，但外层查询已经按YEAR(ReceivedDate)和FORMAT(ReceivedDate, 'MMMM')分组，cc.id_Contact既不在GROUP BY字段中，也未被聚合函数包裹，违反了SQL分组查询的逻辑规则，导致报错。

修正方案：使用条件聚合替代嵌套子查询 #

直接在聚合函数中通过CASE WHEN筛选状态，无需嵌套子查询即可按年月分组统计各状态的投诉数量：

SELECT 
    YEAR(cc.ReceivedDate) AS 'Year',
    FORMAT(cc.ReceivedDate, 'MMMM') AS 'Month',
    COUNT(*) AS 'Received Complaints',
    SUM(CASE WHEN cc.status = 'Resolved' THEN 1 ELSE 0 END) AS 'Resolved Complaints',
    SUM(CASE WHEN cc.status = 'New' THEN 1 ELSE 0 END) AS 'New Complaints'
FROM db.CustomerComplaints cc
JOIN db.ReferralUpdates r
    ON cc.id_Contact = r.Reference
WHERE r.ReferenceCode = 'Project1'
GROUP BY YEAR(cc.ReceivedDate), FORMAT(cc.ReceivedDate, 'MMMM')
ORDER BY YEAR(cc.ReceivedDate), MONTH(cc.ReceivedDate)

关键说明 #

1. 条件聚合逻辑：SUM(CASE WHEN ... THEN 1 ELSE 0 END)会对分组内符合状态条件的记录计数，直接整合到外层分组查询中，避免了子查询关联单个id_Contact的问题。
2. JOIN类型优化：原查询使用LEFT JOIN但通过WHERE r.ReferenceCode = 'Project1'过滤掉了无匹配的记录，和INNER JOIN效果一致，调整为INNER JOIN更贴合业务逻辑。
3. 排序优化：新增ORDER BY YEAR(cc.ReceivedDate), MONTH(cc.ReceivedDate)，确保结果按年份和月份的实际时间顺序排列（避免月份名称按字母排序导致的顺序混乱）。

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