时间戳

首页 > 解决方案 > SQL：同一查询中有两个不同的 SUM

sql - SQL：同一查询中有两个不同的 SUM

问题描述

我正在学习sql，今天我尝试编写两个查询：一个是计算某年的总支出，另一个是计算总收入。他们来了：

--We are interested in the Sum of actual Expenditures for the year 2020
--Grouped by Organization and Department
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 
ORDER BY 
  O.OrganizationName 

--We are interested in the Sum of actual Revenues for the year 2020
--Grouped by Organization and Department
    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 
    ORDER BY 
      O.OrganizationName

现在我想编写一个查询，我可以在其中同时执行所有这些操作（当我运行它时，我希望将两列与总计并排清楚）。我试着在另一个里面写一个，但是结果完全错误：

SELECT O.OrganizationName, D.DepartmentGroupName, SUM(F1.Amount) AS [Total Expenditures], SUM(SUBQUERY.[Total Revenues]) AS [Total Revenues]
FROM
(
SELECT O.OrganizationName, D.DepartmentGroupName, F.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, F.Amount
) AS SUBQUERY,
      FactFinance AS F1
      LEFT JOIN DimOrganization AS O ON F1.OrganizationKey = O.OrganizationKey
      LEFT JOIN DimDepartmentGroup AS D ON F1.DepartmentGroupKey = D.DepartmentGroupKey
      LEFT JOIN DimScenario AS S ON F1.ScenarioKey = S.ScenarioKey
      LEFT JOIN DimAccount AS A ON F1.AccountKey = A.AccountKey
WHERE A.AccountType = 'Expenditures' AND S.ScenarioName = 'Actual' AND YEAR(Date) = 2020
GROUP BY O.OrganizationName, D.DepartmentGroupName

标签： sql

解决方案

假设Amount从DimAccount表中可以使用一种SUMIF方法。

SELECT
    O.OrganizationName,
    D.DepartmentGroupName,
    SUM(CASE WHEN A.AccountType = 'Revenue' THEN Amount END) AS [Total Revenues],
    SUM(CASE WHEN A.AccountType = 'Expenditures' THEN Amount END) 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
    S.ScenarioName = 'Actual'
  AND YEAR(Date) = 2020
GROUP BY
    O.OrganizationName,
    D.DepartmentGroupName
ORDER BY
    O.OrganizationName

推荐阅读