CODEWITHSUNDEEP
sqloracle-database
Marin
Marin

Reputation: 69

Make a difference between two results

I need to make a difference between the results of these 2 queries.

SELECT SUM(overwritten_ecl+ overwritten_ecl_off) A1
FROM llpa.h_rt_ifrs_provision
WHERE calculation_id = 1624
AND stage = 1
AND id_rip_code = 'HOPISD'
AND actual_urg = '0.5'

and this one

SELECT SUM(overwritten_ecl+ overwritten_ecl_off) A2
FROM llpa.h_rt_ifrs_provision
WHERE calculation_id = 1652
AND stage = 1
AND id_rip_code = 'HOPISD'
AND actual_urg = '0.5'

The result of the first query is: 123456 and the second query result is: 2345678. What I need to do is the simple difference between 123456 - 2345678. How can I do it with one query? I tried the MINUS operator, but it didn't work. I am using ORACLE SQL.

Upvotes: 0

Views: 47

Answers (2)

jarlh
jarlh

Reputation: 44796

Use a case expression to do conditional aggregation. SUM positive values for 1624 id's, and negative values for 1652 id's.

SELECT SUM(case calculation_id
             when 1624 then  overwritten_ecl + overwritten_ecl_off
             when 1652 then -overwritten_ecl - overwritten_ecl_off
             else 0
           end)
FROM llpa.h_rt_ifrs_provision
WHERE calculation_id IN (1624, 1652)
AND stage = 1
AND id_rip_code = 'HOPISD'
AND actual_urg = '0.5'

Basically the same answer as Radim's, with the exception that I kept the calculation_id condition in the WHERE clause. May speed things up.

Upvotes: 3

Radim Bača
Radim Bača

Reputation: 10711

Use the conditional aggregation

SELECT SUM(CASE WHEN calculation_id = 1624 THEN overwritten_ecl+ overwritten_ecl_off ELSE 0 END)  - 
       SUM(CASE WHEN calculation_id = 1652 THEN overwritten_ecl+ overwritten_ecl_off ELSE 0 END)  diference
FROM llpa.h_rt_ifrs_provision
WHERE stage = 1
    AND id_rip_code = 'HOPISD'
    AND actual_urg = '0.5'

Upvotes: 0

Related Questions