列在选择列表中无效,因为它不包含在聚合函数或GROUP BY子句中
我试图让此SQL查询从另一个表中获取另外1个东西。但是,当我尝试将它添加到select时,它给我一个错误 - “列'OrderItem.orderItemValue'在选择列表中无效,因为它不包含在聚合函数或GROUP BY子句中。”列在选择列表中无效,因为它不包含在聚合函数或GROUP BY子句中
原来这里是SQL查询(存储过程)的正常工作:
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE SP_InvoiceReports_CustProducts
@custIdent varchar(50) ,
@startDate datetime ,
@endDate datetime
AS
set @endDate = dateAdd(month, 1, @startDate)
SELECT product.styleDesc, product.styleRef , empCust.custName , productSize.sizeChartNme ,
Sum(orderItemAudit.shipQuantity) as theShipment ,
Sum(orderItemAudit.shipValue) as theCost
FROM orderItemAudit INNER JOIN
orders ON
orderItemAudit.orderIdent = orders.orderIdent INNER JOIN
OrderItem ON
orderItemAudit.orderItemIdent = OrderItem.orderItemIdent AND
orders.orderIdent = OrderItem.orderIdent INNER JOIN
empCust ON
orders.custIdent = empCust.custIdent INNER JOIN
product ON OrderItem.styleRef = product.styleRef INNER JOIN
productSize ON OrderItem.sizeChartIdent = productSize.sizeChartIdent
WHERE empCust.custIdent = @custIdent
and orderItemAudit.dateShipped >= @startDate
and orderItemAudit.dateShipped <= @endDate
GROUP BY product.styleRef, product.styleDesc, empCust.custName, productSize.sizeChartNme
ORDER BY product.styleRef, product.styleDesc, empCust.custName, productSize.sizeChartNme
这之后我加我想选择什么变化:
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE SP_InvoiceReports_CustProducts
@custIdent varchar(50) ,
@startDate datetime ,
@endDate datetime
AS
set @endDate = dateAdd(month, 1, @startDate)
SELECT product.styleDesc, product.styleRef , empCust.custName , productSize.sizeChartNme , OrderItem.orderItemValue ,
Sum(orderItemAudit.shipQuantity) as theShipment ,
Sum(orderItemAudit.shipValue) as theCost
FROM orderItemAudit INNER JOIN
orders ON
orderItemAudit.orderIdent = orders.orderIdent INNER JOIN
OrderItem ON
orderItemAudit.orderItemIdent = OrderItem.orderItemIdent AND
orders.orderIdent = OrderItem.orderIdent INNER JOIN
empCust ON
orders.custIdent = empCust.custIdent INNER JOIN
product ON OrderItem.styleRef = product.styleRef INNER JOIN
productSize ON OrderItem.sizeChartIdent = productSize.sizeChartIdent
WHERE empCust.custIdent = @custIdent
and orderItemAudit.dateShipped >= @startDate
and orderItemAudit.dateShipped <= @endDate
GROUP BY product.styleRef, product.styleDesc, empCust.custName, productSize.sizeChartNme
ORDER BY product.styleRef, product.styleDesc, empCust.custName, productSize.sizeChartNme
我不不理解这个,因为那张桌子是INNER JOIN?
任何帮助将不胜感激。
你没有通过表达列添加到组:
GROUP BY product.styleRef, product.styleDesc, empCust.custName, productSize.sizeChartNme, OrderItem.orderItemValue
添加OrderItem.orderItemValue
您GROUP BY
条款:
GROUP BY product.styleRef,
product.styleDesc,
empCust.custName,
productSize.sizeChartNme,
OrderItem.orderItemValue
唯一的问题是,当我这样做时,它似乎没有给我我想要的结果。 – scarhand
不确定你想要的结果。 –
当你做一个GROUP BY
你说“对于所有行是对这些字段具有相同的值,向我展示其他字段的集合“。
当中有一个字段为而不是时,则需要对其进行汇总或排除。
基本上,结果集的每一行都可能表示数十行或数百行的原始数据,如果不使用聚合,SQL并不知道要选择哪些行。
这就是说,如果您将它添加到您的GROUP BY
列表中,它应该可以解决问题。或者,如果某个特定组中的所有行的值应该与该字段的值相同,则可以使用类似MAX()
这样的简单操作来汇总该字段。
对不起Tejs,编辑了错误的答案!我的错!我已经回滚了。抱歉! –