CODEWITHSUNDEEP
sqloracle-databaseoracle11goracle-sqldeveloperoracle-apex
user13768246
user13768246

Reputation:

Join the results from two queries into one in Oracle SQL

I have two queries and I want to join them without creating a function. The first one gives me the result of my current balance. The result is 160€:

select sum(SESSIONS.PRICE) - sum(SESSIONS.AMOUNT)  as v_diff
from SESSIONS
where SESSIONS.CLIENTS_ID =:P2002_ID  
and SESSIONS.STATUS in (4,5);

The second query gives the extra payment. The result is 30€:

select sum(SESSIONS.AMOUNT) as v_extra_amount
from SESSIONS
where SESSIONS.CLIENTS_ID = :P2002_ID 
and SESSIONS.STATUS = 1
and SESSIONS.PAYMENT_STATUS = 2;

So I want if it is possible one query to give me the amount of 130€! (160€ from the first one -30€ from the second).

Upvotes: 1

Views: 70

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270091

Just use conditional aggregation:

select (sum(case when s.STATUS in (4, 5) then s.PRICE else 0 end) - 
        sum(case when s.STATUS in (4, 5) then s.AMOUNT else 0 end) 
       ),
       sum(case when s.STATUS = 2 then s.AMOUNT end)
into v_diff, v_extra_amount
from SESSIONS s
where s.CLIENTS_ID = :P2002_ID;

Or, if you want one value, just subtract:

select (sum(case when s.STATUS in (4, 5) then s.PRICE else 0 end) - 
        sum(case when s.STATUS in (4, 5) then s.AMOUNT else 0 end) -
        sum(case when s.STATUS = 1 and s.PAYMENT_STATUS = 2 then s.AMOUNT end)
       )
from SESSIONS s
where s.CLIENTS_ID = :P2002_ID;

Upvotes: 3

Related Questions