查询SQL服务器 - “不包含在聚合函数或GROUP BY子句”

Question

当我尝试执行此查询：

SELECT 
    EventNo, Customer.CustNo, CustName,EstCost, 
    SUM(EstCost) AS TotalEstCost, COUNT(*) 
FROM 
    EventRequest 
INNER JOIN 
    Customer ON EventRequest.CustNo = Customer.CustNo 
WHERE 
    Status = 'Approved' 
GROUP BY 
    Customer.CustNo; 

我得到这个错误：

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

的表连接在这里

Answer 1

您可能需要GROUP BY一个ll字段不包含在聚合函数中。

SELECT EventNo, c.CustNo, CustName, EstCost, SUM(EstCost) AS TotalEstCost, COUNT(*) 
FROM EventRequest er 
INNER JOIN Customer c ON er.CustNo = c.CustNo 
WHERE Status = 'Approved' 
GROUP BY c.CustNo, EventNo, CustName, EstCost 

或者使用子查询来选择它们。

SELECT (SELECT ers.EventNo 
     FROM EventRequest ers 
     WHERE er.EventNo = ers.EventNo and ers.CustNo = c.CustNo) AS EventNo, 
     c.CustNo, 
     (SELECT cs.CustName 
     FROM Customer cs 
     WHERE c.CustName = cs.CustName AND c.CustNo = cs.CustNo) AS CustName, 
     (SELECT ers2.EstCost 
     FROM EventRequest ers2 
     WHERE er.EventNo = ers2.EventNo and ers2.CustNo = c.CustNo) AS EstCost, 
     SUM(EstCost) AS TotalEstCost, 
     COUNT(*) 
FROM EventRequest er 
INNER JOIN Customer c ON er.CustNo = c.CustNo 
WHERE Status = 'Approved' 
GROUP BY c.CustNo 

Answer 2

您必须添加的所有字段选择语句将GROUP BY之后，你西港岛线得到预期的结果。 例子：

SELECT EventNo, Customer.CustNo, CustName,EstCost, SUM(EstCost) AS TotalEstCost, COUNT(*) 
FROM EventRequest 
    INNER JOIN Customer ON EventRequest.CustNo = Customer.CustNo 
WHERE Status = 'Approved' 
GROUP BY EventNo, Customer.CustNo, CustName,EstCost; 

Answer 3

我怀疑你想客户，而不是总被事件。因此，删除无关列在描述事件的SELECT：

SELECT c.CustNo, c.CustName, SUM(EstCost) AS TotalEstCost, COUNT(*) 
FROM EventRequest er INNER JOIN 
    Customer c 
    ON er.CustNo = c.CustN 
WHERE Status = 'Approved' 
GROUP BY c.CustNo, c.CustName; 

这将返回一行每客户，事件和事件数量的成本一起。

Answer 4

当您使用GROUP BY子句时，只能使用聚合函数（MAX,SUM ...）选择分组列或聚合非分组列。

在您的查询，你被Customer.CustNo groupping，但你去选择EventNo（及其他），这是造成错误。

您需要思考这个专栏，因为选择它是没有意义的。如果您按客户进行分组，则结果集将为每位客户设置一行（如果要合计某些客户数据（如成本），这很有意义），但您希望如何输出多个EventNo单行中的值？你可能不要想要它在那里。 EstCost（不是总数）也是如此。

至于其他字段做才有意义（如CustName），你可以将它们添加到您的GROUP BY条款，那么你可以直接选择他们。

查询更改为：

SELECT Customer.CustNo, CustName, SUM(EstCost) AS TotalEstCost, COUNT(*) 
FROM EventRequest INNER JOIN Customer ON 
    EventRequest.CustNo = Customer.CustNo 
WHERE Status = 'Approved' 
GROUP BY Customer.CustNo, CustName;