如何在同一条SQL查询中同时统计总支出和总收入两个SUM聚合值

错误原因分析 #

你之前的代码出错核心是使用了无关联条件的隐式连接，子查询与支出查询的结果集直接生成笛卡尔积，数据行数被重复放大，因此求和结果完全不符合预期。

最优实现方案（条件聚合） #

推荐直接使用条件聚合实现，仅需单次扫描事实表，性能更好，代码更简洁：

SELECT 
  O.OrganizationName, 
  D.DepartmentGroupName, 
  SUM(CASE WHEN A.AccountType = 'Expenditures' THEN F.Amount ELSE 0 END) AS [Total Expenditures],
  SUM(CASE WHEN A.AccountType = 'Revenue' THEN F.Amount ELSE 0 END) AS [Total Revenues]
FROM 
  FactFinance F 
  LEFT JOIN DimOrganization AS O ON F.OrganizationKey = O.OrganizationKey 
  LEFT JOIN DimDepartmentGroup AS D ON F.DepartmentGroupKey = D.DepartmentGroupKey 
  LEFT JOIN DimScenario AS S ON F.ScenarioKey = S.ScenarioKey 
  LEFT JOIN DimAccount AS A ON F.AccountKey = A.AccountKey 
WHERE 
  S.ScenarioName = 'Actual' 
  AND YEAR(Date) = 2020 
  AND A.AccountType IN ('Expenditures', 'Revenue')
GROUP BY 
  O.OrganizationName, 
  D.DepartmentGroupName 
ORDER BY 
  O.OrganizationName 

子查询关联实现方案 #

如果你更习惯用子查询拆分逻辑，可以先分别聚合支出和收入两个结果集，再通过分组字段关联，避免笛卡尔积：

SELECT
  COALESCE(E.OrganizationName, R.OrganizationName) AS OrganizationName,
  COALESCE(E.DepartmentGroupName, R.DepartmentGroupName) AS DepartmentGroupName,
  ISNULL(E.[Total Expenditures], 0) AS [Total Expenditures],
  ISNULL(R.[Total Revenues], 0) AS [Total Revenues]
FROM
-- 支出聚合子查询
(
  SELECT 
    O.OrganizationName, 
    D.DepartmentGroupName, 
    SUM(Amount) AS [Total Expenditures] 
  FROM 
    FactFinance F 
    LEFT JOIN DimOrganization AS O ON F.OrganizationKey = O.OrganizationKey 
    LEFT JOIN DimDepartmentGroup AS D ON F.DepartmentGroupKey = D.DepartmentGroupKey 
    LEFT JOIN DimScenario AS S ON F.ScenarioKey = S.ScenarioKey 
    LEFT JOIN DimAccount AS A ON F.AccountKey = A.AccountKey 
  WHERE 
    A.AccountType = 'Expenditures' 
    AND S.ScenarioName = 'Actual' 
    AND YEAR(Date) = 2020 
  GROUP BY 
    O.OrganizationName, 
    D.DepartmentGroupName 
) E
FULL OUTER JOIN
-- 收入聚合子查询
(
  SELECT 
    O.OrganizationName, 
    D.DepartmentGroupName, 
    SUM(Amount) AS [Total Revenues] 
  FROM 
    FactFinance F 
    LEFT JOIN DimOrganization AS O ON F.OrganizationKey = O.OrganizationKey 
    LEFT JOIN DimDepartmentGroup AS D ON F.DepartmentGroupKey = D.DepartmentGroupKey 
    LEFT JOIN DimScenario AS S ON F.ScenarioKey = S.ScenarioKey 
    LEFT JOIN DimAccount AS A ON F.AccountKey = A.AccountKey 
  WHERE 
    A.AccountType = 'Revenue' 
    AND S.ScenarioName = 'Actual' 
    AND YEAR(Date) = 2020 
  GROUP BY 
    O.OrganizationName, 
    D.DepartmentGroupName 
) R
ON E.OrganizationName = R.OrganizationName AND E.DepartmentGroupName = R.DepartmentGroupName
ORDER BY OrganizationName

此处使用FULL OUTER JOIN和COALESCE是为了避免某个分组只有支出没有收入、或只有收入没有支出时数据丢失，用ISNULL将空值转为0更符合业务统计习惯。

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