CODEWITHSUNDEEP
sqloracle-databasesumaggregate-functions
Joesph
Joesph

Reputation: 71

Oracle sql - Selecting the difference between two sums from two different tables?

Say I have two tables, which I want to calculate their sum differences:

company1(
id INTEGER,
salary INTEGER
)

company2(
id INTEGER,
salary INTEGER
)

How can I select the difference between their SUM(salary) from each of their employees using only one select query?

For example:

SELECT 
(SELECT SUM(company1.salary) FROM company1 GROUP BY id) - (SELECT SUM(company2.salary) FROM company2 GROUP BY id) as diff_between_pay

The end results would be like:

company1
id   salary  
1    40000
2    20000
3    50000

company2
id   salary
1    30000
2    15000
3    25000

end result differences:
id
1    10000
2    5000
3    25000

Upvotes: 0

Views: 2619

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270081

You can do this in a select:

select (select sum(salary) from company1) - (select sum(salary) from company2) as diff_between_pay
from dual;

For your revised question, use a full join:

select ( coalesce(c1.sums, 0) - coalesce(c2.sums, 0) ) as diff_between_pay
from (select id, sum(salary) as sums
      from company1
      group by id
     ) c1 full join
     (select id, sum(salary) as sums
      from company2
      group by id
     ) c2
     on c1.id = c2.id;

Upvotes: 3

Related Questions