SQL求和问题：如何在SSMS中汇总INR与AUD的分组计算结果

问题说明 #

刚接触SQL学习，需要汇总两个分组计算结果：分别统计Currency为INR且Mkt_Price>100、Currency为AUD且Mkt_Price>100时，按Pricing_factor分组的SUM(Mkt_Price)*Pricing_factor值，尝试汇总时持续报错，现有代码如下：

SELECT TOP (1000) [Sec_id]
      ,[Prc_date]
      ,[Mkt_Price]
      ,[Currency]
      ,[Pricing_factor]
  FROM [EXAMPLE1].[dbo].[market]
  WHERE Currency='INR' OR Currency='AUD';
  
SELECT  SUM(Mkt_Price)* Pricing_factor as INR FROM market 
where Currency='INR' and Mkt_Price>100 group by Pricing_factor; 

SELECT SUM(Mkt_Price)* Pricing_factor as AUD FROM market 
where Currency='AUD' and Mkt_Price>100 group by Pricing_factor;

SELECT  SUM(Mkt_Price)* Pricing_factor as totalll FROM market 
where Currency='AUD' or Currency='INR' and Mkt_Price>100 group by Pricing_factor

错误原因 #

最后一个汇总查询的WHERE条件逻辑错误：Currency='AUD' or Currency='INR' and Mkt_Price>100 会被SQL解析为 (Currency='AUD') OR (Currency='INR' AND Mkt_Price>100)，导致所有AUD的行（无论Mkt_Price是否大于100）都被计入，不符合需求。

解决方案 #

方法1：条件聚合（推荐） #

在单个查询中分别计算两种货币的分组值，同时得到总和，逻辑清晰且效率更高：

SELECT 
    Pricing_factor,
    -- 计算INR对应的分组值
    SUM(CASE WHEN Currency = 'INR' THEN Mkt_Price ELSE 0 END) * Pricing_factor AS INR_Total,
    -- 计算AUD对应的分组值
    SUM(CASE WHEN Currency = 'AUD' THEN Mkt_Price ELSE 0 END) * Pricing_factor AS AUD_Total,
    -- 计算两者总和
    SUM(Mkt_Price) * Pricing_factor AS Total
FROM [EXAMPLE1].[dbo].[market]
WHERE Currency IN ('INR', 'AUD') AND Mkt_Price > 100
GROUP BY Pricing_factor;

方法2：合并子查询结果再求和 #

先通过UNION ALL合并两个分组查询的结果，再重新分组求和：

WITH CurrencyGroupTotals AS (
    -- 取出INR的分组计算结果
    SELECT Pricing_factor, SUM(Mkt_Price)*Pricing_factor AS GroupTotal
    FROM [EXAMPLE1].[dbo].[market]
    WHERE Currency='INR' AND Mkt_Price>100
    GROUP BY Pricing_factor
    UNION ALL
    -- 取出AUD的分组计算结果
    SELECT Pricing_factor, SUM(Mkt_Price)*Pricing_factor AS GroupTotal
    FROM [EXAMPLE1].[dbo].[market]
    WHERE Currency='AUD' AND Mkt_Price>100
    GROUP BY Pricing_factor
)
-- 按Pricing_factor汇总总和
SELECT 
    Pricing_factor,
    SUM(GroupTotal) AS Total
FROM CurrencyGroupTotals
GROUP BY Pricing_factor;

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