CODEWITHSUNDEEP
sqlsql-server
Empyz
Empyz

Reputation: 59

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

I have the following two tables and I want to find the difference between table 1 and table 2 for the month of "January". The difference should be the sum of all values for January in table 1 minus the sum of all values for the month of January in table 2.

The answer should be 300 (800 - 500), but my query keeps giving me the value "600".

My query is:

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

Table 1

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

Table 2

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

Upvotes: 2

Views: 9231

Answers (2)

Laughing Vergil
Laughing Vergil

Reputation: 3756

The problem is that you are summing the values once for each row matching 'January' in A. This results in both values being doubled, and gives you double the difference.

To work around this, you must calculate the SUMmed values separately. Here is one way to do it:

select 'January'
    (SELECT sum(cost2) from Table1 where [month]='January') - 
    (SELECT sum(cost2) from Table2 where month='January') as difference

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270523

One method is to aggregate first and then join:

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;

Upvotes: 4

Related Questions