如何对具有聚合功能的oracle查询进行分组

Question

上面写着"不是groupby函数"。当我将D.unitpricef添加到groupby时，它没有显示任何错误，但在结果中它多次显示相同的itmcode。一个项目代码应该只显示一次

SELECT 
ItemCode, 
case when H.InvoType = 1 then concat(ItemCode,' ( SLT Equipment )' ) else concat(ItemCode,' ( 
CONBES Eqipment )' ) end as EquName,
case when TRIM(SUM(QTY)) is null then ''  else TRIM(TO_CHAR(SUM(QTY),'999999')) end as QTY,
CAST( (SUM(QTY) * D.unitpricef) AS NUMBER(38,2)) AS Amount
FROM Invoicedetails D 
INNER JOIN  
invoiceheader H ON D.InvoiceNo = H.InvoiceNo 
INNER JOIN Equipment E ON E.EquCode = ItemCode
WHERE 
H.CancelStat= 0 
AND H.ReceiptStat = 1  
AND H.BCCODE = 'xxx'
GROUP BY ItemCode,H.InvoType ORDER BY ItemCode ASC;

Answer 1

您需要使用与聚合函数关联的相同表达式来group by

SELECT 
ItemCode, 
case when H.InvoType = 1 then concat(ItemCode,' ( SLT Equipment )' ) else concat(ItemCode,' ( CONBES Eqipment )' ) end as EquName,
case when TRIM(SUM(QTY)) is null then ''  else TRIM(TO_CHAR(SUM(QTY),'999999')) end as QTY,
CAST( (SUM(QTY) * D.unitpricef) AS NUMBER(38,2)) AS Amount
FROM Invoicedetails D 
INNER JOIN  
invoiceheader H ON D.InvoiceNo = H.InvoiceNo 
INNER JOIN Equipment E ON E.EquCode = ItemCode
WHERE 
H.CancelStat= 0 
AND H.ReceiptStat = 1  
AND H.BCCODE = 'xxx'
GROUP BY 
ItemCode
case when H.InvoType = 1 then concat(ItemCode,' ( SLT Equipment )' ) else concat(ItemCode,' ( CONBES Eqipment )' ) end 

ORDER BY ItemCode ASC;