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.