如何在现有SQL查询中新增列计算Failed与Success的聚合和？

你完全可以在现有查询的SELECT子句中直接添加一个新的聚合计算列，不需要额外创建表或拆分查询，有两种简洁的实现方式：

方法1：复用已计算的聚合列 #

既然你已经算出了Failed和Success的数量，直接把这两个列相加就能得到它们的总和，这种方式的好处是如果后续调整Failed或Success的判断逻辑，总和会自动同步：

SELECT 
  distinct COUNT(Status) AS [Transactions], 
  sending_organisation AS [Supplier], 
  DATENAME(mm, Date_Reported) AS Month, 
  DATENAME(yyyy, Date_Reported) AS Year, 
  Sum(Case When Status = 'Defect' Then 1 Else 0 End) As Defect, 
  Sum(Case When Status = 'Failed' Then 1 Else 0 End) As Failed, 
  Sum(Case When Status = 'Success' Then 1 Else 0 End) As Success,
  -- 新增的Failed与Success总和列
  Sum(Case When Status = 'Failed' Then 1 Else 0 End) + Sum(Case When Status = 'Success' Then 1 Else 0 End) As Failed_Success_Total
FROM [Tx] 
where 
  Channel_Partner = 'CAT' 
  and DATENAME(yyyy, Date_Reported) = '2018' 
  and DATENAME(mm, Date_Reported) = 'March' 
GROUP BY 
  DATENAME(mm, Date_Reported), 
  DATENAME(yyyy, Date_Reported), 
  sending_organisation 
ORDER BY sending_organisation ASC

方法2：直接聚合符合条件的记录 #

更简洁的方式是在一个CASE表达式里同时匹配Failed和Success状态，一次计算出总和：

SELECT 
  distinct COUNT(Status) AS [Transactions], 
  sending_organisation AS [Supplier], 
  DATENAME(mm, Date_Reported) AS Month, 
  DATENAME(yyyy, Date_Reported) AS Year, 
  Sum(Case When Status = 'Defect' Then 1 Else 0 End) As Defect, 
  Sum(Case When Status = 'Failed' Then 1 Else 0 End) As Failed, 
  Sum(Case When Status = 'Success' Then 1 Else 0 End) As Success,
  -- 新增的Failed与Success总和列
  Sum(Case When Status In ('Failed', 'Success') Then 1 Else 0 End) As Failed_Success_Total
FROM [Tx] 
where 
  Channel_Partner = 'CAT' 
  and DATENAME(yyyy, Date_Reported) = '2018' 
  and DATENAME(mm, Date_Reported) = 'March' 
GROUP BY 
  DATENAME(mm, Date_Reported), 
  DATENAME(yyyy, Date_Reported), 
  sending_organisation 
ORDER BY sending_organisation ASC

注意事项 #

原查询里有个小语法问题：Success列后面多了一个逗号，会导致SQL执行报错，上面的代码已经帮你修正了这个问题。

内容的提问来源于stack exchange，提问作者Taz