这是我的查询，但有一列我不想分组，但我希望它在when条件中使用

Question

这里我在case语句中使用sr.SourceTransactionType，但我没有选择它。但代码不允许我按sr.SourceTransactionType将sr.SourceTradeType与out group一起使用所以请给我建议

BEGIN
SELECT it.Description AS ItemType
,iv.ItemCode
,iv.Description AS ItemName
,un.Description AS Unit
,CASE 
WHEN sr.SourceTransactionType = 'OBL'
THEN SUM(sr.Quantity)
END AS 'OBLQTY'
,CASE 
WHEN sr.SourceTransactionType = 'OBL'
THEN SUM(sr.Quantity * sr.UnitRate)
END AS 'OBLAMOUNT'
,CASE 
WHEN sr.SourceTransactionType = 'GRND'
THEN SUM(sr.Quantity)
END AS 'GRNQTY'
,CASE 
WHEN sr.SourceTransactionType = 'GRND'
THEN SUM(sr.Quantity * sr.UnitRate)
END AS 'GRNAMOUNT'
,CASE 
WHEN sr.SourceTransactionType = 'RCPT'
THEN SUM(sr.Quantity)
END AS 'RCPTQTY'
,CASE 
WHEN sr.SourceTransactionType = 'RCPT'
THEN SUM(sr.Quantity * sr.UnitRate)
END AS 'RCPTAMOUNT'
,SUM(sr.IssuedQuantity) AS IssuedQty
,SUM(sr.IssuedQuantity * sr.UnitRate) AS IssuedAmount
,SUM(sr.IssueReturnQuantity) AS IssRtnQty
,SUM(sr.IssueReturnQuantity * sr.UnitRate) AS IssRtnAmount
,SUM(sr.DispatchedQuantity) AS DispatchQty
,SUM(sr.DispatchedQuantity * sr.UnitRate) AS DispatchAmount
FROM [dbo].[sms_StockRegister] sr
INNER JOIN [dbo].[sms_ItemVersion] iv ON sr.ItemId = iv.ItemId
INNER JOIN [dbo].[cmn_ItemType] it ON iv.ItemTypeId = it.ItemTypeId
INNER JOIN [dbo].[cmn_Unit] un ON iv.UnitId = un.UnitId
GROUP BY iv.Description,it.Description,iv.ItemCode
,ac4.AccountCodeGN4Code,un.Description
ORDER BY iv.Description
END

Answer 1

您可以在aggregate function中使用CASE表达式，如下所示：

更换

CASE WHEN sr.SourceTransactionType = 'OBL' THEN SUM(sr.Quantity) END AS 'OBLQTY'

带有

SUM(CASE WHEN sr.SourceTransactionType = 'OBL' THEN sr.Quantity END) AS 'OBLQTY'

您需要在查询中的其他聚合函数中以相同的方式进行更改。