CREATE TABLE TEST(TRANS_ID,ACC_ID,TRANS_TYPE,AMOUNT)AS
(
SELECT 123,101,'DEPOSIT',10 FROM DUAL UNION ALL
SELECT 124,101,'DEPOSIT',30 FROM DUAL UNION ALL
SELECT 125,101,'WITHDRAWAL',5 FROM DUAL UNION ALL
SELECT 126,201,'DEPOSIT',20 FROM DUAL UNION ALL
SELECT 128,201,'WITHDRAWAL',10 FROM DUAL
)
select * from test
select acc_id,
sum(case when trans_type = 'withdrawal' then amount*(-1) else amount end) as final_balance
from test
group by acc_id
my result:
ACC_IDFINAL_BALANCE
101 45
201 30
expected result:
acc_id final_balance
101 45
201 -15