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

I have come up with these two queries to find the sum of a field on a single table based on specific where clauses:

我已经提出了这两个查询，以根据特定的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

I'm having trouble figuring out a way to sum the results of both queries together to get a Grand Total. Any suggestions as to how I could do this?

我无法找到一种方法来将两个查询的结果相加以获得总计。有关如何做到这一点的任何建议？

2 个解决方案

#1

Just add [TRAN ID] 200 to IN clause :

只需将[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);

To get all sums:

获得所有金额：

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);

#2

Since the rows producing these sums are mutually exclusive, you could just combine the [TRAN ID]s in their where clauses:

由于产生这些总和的行是互斥的，您可以在其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)

#1