Skip to content
Advertisement

Performing subtraction in SQL/finding the balance

SQL table here: http://sqlfiddle.com/#!9/abe1da/9

Current Table:

Year Month Type Accounts Amount
2021 1 Actual abc 20
2021 1 Actual def 30
2021 1 Actual ghi 40
2021 1 Actual X 7
2021 1 Actual Y 3
2021 1 Actual final 105

Expected

Year Month Type Accounts Amount
2021 1 Actual abc 20
2021 1 Actual def 30
2021 1 Actual ghi 40
2021 1 Actual X 7
2021 1 Actual Y 3
2021 1 Actual final 105
2021 1 Actual BALANCE 5

SQL Attempt

select year, month, type,
case when accounts in ('abc') then 'abc'
 when accounts in ('def') then 'def'
 when accounts in ('ghi') then 'ghi'
 when accounts in ('X') then 'x'
 when accounts in ('Y') then 'Y'
 when accounts in ('final') then 'final'
else 'balance'
end as account_2
,
sum
(
(case when accounts in ('abc','def','ghi','final','x','y') then amount
else 
(
(case when accounts in ('final') then (amount))-
(case when accounts in ('abc','def','ghi','x','y') then -amount else 0))
)
from new

Note: That balance of 5 represents thousands of small accounts which currently do not form part of a database.

Advertisement

Answer

If I understand correctly:

select Year, Month, Type, Accounts, Amount
from new
union all
select year, month, type, 'balance',
       (sum(case when accounts = 'final' then amount else 0 end) -
        sum(case when accounts <> 'final' then amount else 0 end) 
       )
from new
group by year, month, type;

Here is a db<>fiddle.

8 People found this is helpful
Advertisement