来自同一张表的两个 SQL 查询结果求和

Question

我想到了这两个查询语句，可以基于特定的where子句在单个表格上找到一个字段的总和：

Select Sum(dbo.Charges.[TRANS AMT]) as Total1  
from dbo.Charges 
where (dbo.Charges.[TRAN ID] IN (100, 20, 50, 52, 55, 60, 600))   

Select Sum(dbo.Charges.[TRANS AMT]) as Total2  
from dbo.Charges 
where dbo.Charges.[TRAN ID] = 200

我在想如何将两个查询结果相加以获得总计。你有什么建议吗？

- KAL077

Answer 1

只需将[TRAN ID] 200添加到IN子句中即可：

SELECT SUM(dbo.Charges.[TRANS AMT]) AS GRAND_TOTAL
FROM dbo.Charges 
WHERE dbo.Charges.[TRAN ID] IN (100, 20, 50, 52, 55, 60, 600, 200);

获取所有总和：

SELECT
   SUM(CASE WHEN dbo.Charges.[TRANS AMT] <> 200 THEN dbo.Charges.[TRANS AMT] END) AS Total1,
   SUM(CASE WHEN dbo.Charges.[TRANS AMT] = 200 THEN dbo.Charges.[TRANS AMT] END) AS Total2,
   SUM(dbo.Charges.[TRANS AMT]) AS GRAND_TOTAL
FROM dbo.Charges 
WHERE dbo.Charges.[TRAN ID] IN (100, 20, 50, 52, 55, 60, 600, 200);

- Lukasz Szozda

---

那很简单，不是吗？我过于复杂化了。谢谢！ - KAL077

Answer 2

由于产生这些总和的行是互斥的，因此您可以在它们的where子句中组合[TRAN ID]：

SELECT SUM(dbo.Charges.[TRANS AMT]) as GrandTotal
FROM   dbo.Charges
WHERE  dbo.Charges.[TRAN ID] IN (100, 20, 50, 52, 55, 60, 600, 200)

- Mureinik

---

感谢您的帮助！ - KAL077