Oracle - find difference between columns by conditions

user_5558021 0
Viewed 71

Table with operations:

ID | ID_CUSTOMER | AMOUNT_TRANSFER | ID_ACCOUNT_SENDER | ID_ACCOUNT_RECEIVER|

I have to find ID_CUSTOMER with zero and maximum balance on accounts.

Balance is total difference of all transferred AMOUNT_TRANSFER by accounts.

I tried to find all transfer amounts by customer, but I don't know how find the difference:

SELECT 
    ACCOUNT.ID, SUM(O.AMOUNT_TRANSFER), 
    C2.SECOND_NAME AS Фамилия, C2.FIRST_NAME 
FROM
    ACCOUNT
JOIN 
    CUSTOMER C2 on ACCOUNT.ID_CUSTOMER = C2.ID
JOIN 
    OPERATION O on ACCOUNT.ID = O.ID_ACCOUNT_RECEIVER
GROUP BY 
    ACCOUNT.ID, C2.SECOND_NAME, C2.FIRST_NAME

Example data:

ID  ID_CUSTOMER AMOUNT_TRANSFER ID_ACCOUNT_SENDER ID_ACCOUNT_RECEIVER
1   1   5000    1   2
2   2   3000    1   2
3   1   2000    1   2
4   3   2000    2   3
5   3   1000    3   2

then for example,

 ID_ACCOUNT == 2 have 5000 + 3000 + 2000 + 1000 are received and 2000 are sended , total balance is 11000 - 2000 = 9000,

next I need balance of all accounts (by account) next I will can calculate balance by customer (just JOIN and SUM) and account with 0 and max (by the same approach)

oracle sql
2 Answers

You can split each transfer for the sender and receiver and then aggregate by customer.

The following gives the totals for all customers from transfers:

select a.id_customer, sum(amount) as balance
from ((select id_account_sender as id_account,- amount_transfer as amount
       from operations o
      ) union all
      (select id_account_receiver, amount_transfer
       from operations o
      )
     ) o join
     accounts a
     on a.id = o.id_account
order by sum(amount);
user_11440350

this will work:

select m."a" account_id,nvl(((select sum(o."c") from Table1 o where o."e"=m."b")-
           (select sum(o2."c") from Table1 o2 where o2."d"=m."b")),0)total_balance
           from Table1 m ;

check fiddle:http://sqlfiddle.com/#!4/61fec2/14

for next one(balance by customer):

select customer_id,sum(total_balance) from(select m."a" account_id,m."b" 
customer_id,nvl(((select sum(o."c") from Table1 o where o."e"=m."b")-
(select sum(o2."c") from Table1 o2 where o2."d"=m."b")),0)total_balance
from Table1 m ) group by customer_id  ;

check fiddle:http://sqlfiddle.com/#!4/61fec2/18

for the last one (for account with 0 and max balances):

select e.account_id,case when rn=(select min(d.rn) from(select 
n.*,dense_rank() over ( order by total_balance   )rn from(select m."a" 
account_id,nvl(((select sum(o."c") from Table1 o where o."e"=m."b")-
(select sum(o2."c") from Table1 o2 where o2."d"=m."b")),0)total_balance
from Table1 m)n)d) then 'minimum balance'
when rn=(select max(d.rn) from(select n.*,dense_rank() over ( order by 
total_balance   )rn from(select m."a" account_id,nvl(((select sum(o."c") 
from Table1 o where o."e"=m."b")-
(select sum(o2."c") from Table1 o2 where o2."d"=m."b")),0)total_balance
from Table1 m)n)d) then 'maximum balance'
else
'intermediate balance'end
from
(select n.*,dense_rank() over ( order by total_balance)rn  
from(select m."a" account_id,nvl(((select sum(o."c") from Table1                  
o where 
o."e"=m."b")-
(select sum(o2."c") from Table1 o2 where 
o2."d"=m."b")),0)total_balance
from Table1 m)n)e
order by e.account_id;

check fiddle:http://sqlfiddle.com/#!4/61fec2/42

Thanks!!!!!!! :-)

user_62856000
Related