SQL查询仅按Year分组多列报错，求解决方法

报错的核心是：你的查询在分组（GROUP BY）时仅按Year聚合，但d.LifeType被用于CASE逻辑中，它既没有被包含在GROUP BY子句里，也没有被聚合函数（如SUM、AVG）包裹。SQL要求分组查询中，所有非聚合列必须出现在GROUP BY里，否则无法确定如何将多行的LifeType值合并为分组后的单一值。

根据业务逻辑需求，有两种可行的修正方式：

方案1：先计算每行的维护成本再求和 #

这种方式先为每行计算对应的维护成本，再将所有行的结果求和，符合仅按Year分组的要求，同时保留每行LifeType对应的费率计算：

SELECT
    YEAR(a.ProjectStartDate) AS [Year],
    '' AS 'NetValue',
    CEILING(SUM(CAST(a.DevEffortHours AS DECIMAL(18,2)) * @InternalRate)) AS 'DevelopmentCost',
    CEILING(SUM(
        CEILING(CAST(a.DevEffortHours AS DECIMAL(18,2)) * @InternalRate) 
        * @InternalRate 
        * CASE 
            WHEN d.LifeType = '2-3 years' THEN 2.5
            WHEN d.LifeType = '5 years' THEN 5
            ELSE 1
        END
    )) AS 'MaintenanceCost'
FROM WREF_WIPTrackerKAIMainEntry a
INNER JOIN WREF_WIPTrackerKaiProductMaster b ON b.ID = a.KAIProjectID
INNER JOIN WREF_WIPTrackerStatusMaster c ON c.ID = a.StatusID
INNER JOIN WREF_WIPTrackerProductLifeMaster d ON d.ID = a.ProductLifeID
WHERE c.StatusType = 'Live'
GROUP BY YEAR(a.ProjectStartDate)

方案2：聚合费率后计算总维护成本 #

如果你的业务逻辑要求先计算总开发成本，再乘以一个统一的费率（基于分组内的LifeType），可以用聚合函数（如AVG、MAX、MIN）将分组内的费率合并为单一值。示例使用AVG（根据实际需求替换为合适的聚合函数）：

SELECT
    YEAR(a.ProjectStartDate) AS [Year],
    '' AS 'NetValue',
    CEILING(SUM(CAST(a.DevEffortHours AS DECIMAL(18,2)) * @InternalRate)) AS 'DevelopmentCost',
    CEILING(
        CEILING(SUM(CAST(a.DevEffortHours AS DECIMAL(18,2)) * @InternalRate)) 
        * @InternalRate 
        * AVG(CASE 
            WHEN d.LifeType = '2-3 years' THEN 2.5
            WHEN d.LifeType = '5 years' THEN 5
            ELSE 1
        END)
    ) AS 'MaintenanceCost'
FROM WREF_WIPTrackerKAIMainEntry a
INNER JOIN WREF_WIPTrackerKaiProductMaster b ON b.ID = a.KAIProjectID
INNER JOIN WREF_WIPTrackerStatusMaster c ON c.ID = a.StatusID
INNER JOIN WREF_WIPTrackerProductLifeMaster d ON d.ID = a.ProductLifeID
WHERE c.StatusType = 'Live'
GROUP BY YEAR(a.ProjectStartDate)

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