CODEWITHSUNDEEP
sql
Piotrek Leśniak
Piotrek Leśniak

Reputation: 83

Find the difference for results from two select

I have two tables: 

table_1:  
A | B | C  
z | x | 12  
z | c | 13  
z | c | 10  
a | s | 14  
a | d | 11  

table_2:  
A | B | C  
z | c | 10  
z | x | 15  
z | x | 11  
a | d | 14  
a | s | 12

I want to:
- group the tables by A and B
- and find the difference for SUM of C for AB.

I started with: 

SELECT A, B, SUM(C) from table_1 GROUP BY A, B;  
SELECT A, B, SUM(C) from table_2 GROUP BY A, B;

but I don't know how to JOIN them with adding additional column that is equal to table_1.sum(C) - table_2.sum(c)

Expected result like:

A | B | sum1 | sum2 | diff  
z | x | 12   | 26   | -14 
z | c | 23   | 10   | 13
a | s | 14   | 12   | 2
a | d | 11   | 14   | -3

Upvotes: 0

Views: 29

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

What do you want to happen when the groups are not the same in the two tables? inner join can be dangerous because groups will disappear.

If you want to keep all groups, then one method is union all/group by:

select a, b, sum(c1) as sum1, sum(c2) as sum2,
       (sum(c2) - sum(c1)) as diff
from ((select a, b, c as c1, 0 as c2
       from table_1
      ) union all
      (select a, b, 0 as c1, c as c2
       from table_2
      )
     ) t
group by a, b

Upvotes: 0

Fahmi
Fahmi

Reputation: 37473

Use join with subquery

select X.A,X.B, sum1, sum2, sum1-sum2 as diff from 
(
SELECT A, B, SUM(C) sum1
from table_1 GROUP BY A, B  
)X inner join
(
SELECT A, B, SUM(C) sum2
from table_2 GROUP BY A, B
)Y on X.A=Y.A and X.B=Y.B

Upvotes: 1

Related Questions