SQL：同一查詢中的兩個不同的SUM值

我正在學習sql，今天我試圖寫兩個查詢：一個是計算某一年的總支出，另一個是計算總收入。它們是這樣的：

--我們對2020年的實際支出之和感興趣。
--按組織和部門分類。
SELECT 
  O.OrganizationName, 
  D.DepartmentGroupName。
  SUM(Amount) AS [總支出] 
FROM[總支出]。
  事實財務部F 
  LEFT JOIN DimOrganization AS O ON F.OrganisationKey = 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[/span 
  A.AccountType ='Expenditures' 
  and S.ScenarioName = 'Actual'  
  and YEAR(Date) = 20 
GROUPBY 
  O.OrganizationName, 
  D.DepartmentGroupName 
訂單 BY 
  O.OrganizationName 

--我們對2020年的實際收入之和感興趣。
--按組織和部門分組。
    SELECT 
      O.OrganizationName, 
      D.DepartmentGroupName。
      SUM(Amount) AS [總收入] 
    FROM[總收入 
      事實金融F 
      LEFT JOIN DimOrganization AS O ON F.OrganisationKey = 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[/span 
      A.AccountType ='Revenue' 
      AND S.ScenarioName = 'Actual'  
      and YEAR(Date) = 20 
    GROUPBY 
      O.OrganizationName, 
      D.DepartmentGroupName 
    訂單 BY 
      O.OrganizationName

現在我想寫一個查詢，在那里我可以同時做這些事情（當我運行它時，我想讓這兩列的總數并排在一起，以便明確）。我試著把一個寫在另一個里面，但結果是完全錯誤的：

AS [Total Expenditures], SUM(SUBQUERY.[Total Revenues]) AS [Total Revenues] FROM[/span ( SELECT O.OrganizationName, D.DepartmentGroupName, F.Amount AS [ 總收入] FROM FactFinance F LEFT JOIN DimOrganization AS O ON F.OrganisationKey = 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' ANDS. ScenarioName = 'Actual' AND YEAR(Date) = 2020) GROUP BY O.OrganizationName, D.DepartmentGroupName, F.Amount ) AS SUBQUERY, FactFinance AS F1 LEFT JOIN DimOrganization AS O ONF1.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' ANDS. ScenarioName = 'Actual' AND YEAR(Date) = 2020) GROUP BY O.OrganizationName, D.DepartmentGroupName

uj5u.com熱心網友回復：

假設Amount來自DimAccount表，一個SUMIF的方法將發揮作用。

SELECT
    O.OrganizationName,
    D.DepartmentGroupName,
    SUM(CASE WHENA. AccountType = 'Revenue' THEN Amount END) as[總收入]。
    SUM(CASE WHENA. AccountType = 'Expenditures' THEN Amount END) as [總支出]
FROM[/span]}。
    事實財務F
    LEFT JOIN DimOrganization AS O ON F.OrganisationKey = 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
GROUPBY
    O.OrganizationName,
    D.DepartmentGroupName
訂單 BY
    O.OrganizationName

標籤：

上一篇：選擇陳述句，回傳從當前時間起300分鐘內的資料

下一篇：我怎樣才能在一行中加入每個數字列的平均值呢？