SQL查询报错：Orders.OrderID不在聚合函数或GROUP BY子句中求解

报错原因 #

你遇到的错误是因为SQL Server的聚合函数规则：当SELECT语句中包含聚合函数（如SUM()）时，所有非聚合的列必须出现在GROUP BY子句中，确保每个分组的非聚合列值唯一。你的原代码缺少GROUP BY，导致数据库无法确定如何分组计算聚合值。

修正后的SQL代码 #

SELECT 
    O.OrderID,
    YEAR(O.OrderDate) AS OrderYear,
    DATEPART(qq, O.OrderDate) AS OrderQuarter,
    CONCAT(E.FirstName, ' ', E.LastName) AS EmployeeName,
    C.CustomerName,
    S.ShipperName,
    SUM(D.Quantity) AS OrderItems,
    SUM(D.Quantity * D.UnitPrice) AS OrderRevenue -- 新增订单收入计算
FROM
    Orders AS O
JOIN Employees AS E ON O.EmployeeID = E.EmployeeID
JOIN Customers AS C ON O.CustomerID = C.CustomerID
JOIN Shippers AS S ON O.ShipperID = S.ShipperID
JOIN OrderDetails AS D ON O.OrderID = D.OrderID
GROUP BY 
    O.OrderID,
    YEAR(O.OrderDate),
    DATEPART(qq, O.OrderDate),
    E.FirstName, E.LastName,
    C.CustomerName,
    S.ShipperName
ORDER BY 
    OrderRevenue DESC; -- 按收入降序排序

关键修改说明 #

1. 添加GROUP BY子句：将所有SELECT中的非聚合列（或能唯一决定这些列的原始字段）加入分组，符合SQL Server的语法要求。用E.FirstName和E.LastName分组比直接用拼接后的EmployeeName更高效，且结果一致。
2. 新增OrderRevenue列：通过SUM(D.Quantity * D.UnitPrice)计算每个订单的总营收，利用OrderDetails表中的UnitPrice字段完成计算。
3. 添加ORDER BY：指定按OrderRevenue降序排列，满足排序需求。

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