SQL Server : SUM Values and find the difference from two different tables

Question

我有以下两个表，我想找出“一月”月份表 1 和表 2 之间的差异。差值应为表 1 中 1 月份所有值的总和减去表 2 中 1 月份所有值的总和。

答案应该是 300 (800 - 500)，但我的查询一直给出值“600”。

我的查询是:

SELECT 
    a.month,
    (SUM(a.cost2)) - (SUM(b.cost2)) AS difference
FROM 
    Table1 a
LEFT JOIN 
    Table2 b ON a.Month = b.Month
WHERE 
    a.month = 'January'
GROUP BY 
    a.month

表1

Month    Cost2
--------------
January  500
February 400
March    300
April    600
January  300
March    200
March    400
April    200

表2

Month    Cost2
--------------
January  200
February 250
March    195
April    700
January  300
February 200
March    400
April    200

Answer 1

一种方法是先聚合，然后加入:

select a.month, (a_cost2 - b_cost2) as difference
from (select month, sum(a.cost2) as a_cost2
      from Table1 a
      where a.month = 'January'
      group by month
     ) a left join
     (select b.month, sum(b.cost2) as b_cost2
      from Table2 b
      where b.month = 'January'
      group by month
     ) b
     on a.Month = b.Month;