How to get balance from two SQL table debit and credit dynamically

Question

Customer table

id |    name      |  customerid 
1  | Philip James |   ac1001 
2  | Frank Mathew |   ac1002

Credit table

id| year  | customer |  amount  
1 | 2020  | ac1001   |  1000
2 | 2020  | ac1001   |  1000
3 | 2020  | ac1001   |  1000
4 | 2020  | ac1001   |  1000
5 | 2019  | ac1001   |  1000
6 | 2019  | ac1001   |  2000
7 | 2020  | ac1002   |  2000
8 | 2020  | ac1002   |  2000

Debit table

id| year |  customer|   amount  
1 | 2020 |  ac1001  |   1000
2 | 2020 |  ac1001  |   1000
3 | 2020 |  ac1001  |   1000
4 | 2020 |  ac1001  |   1000
5 | 2019 |  ac1001  |   2000
6 | 2019 |  ac1001  |   2000
7 | 2020 |  ac1002  |   2000
8 | 2020 |  ac1002  |   2000

I am trying to get the balance for each customer dynamically in respect to the year, i tried using this;

 SELECT debit.year,customers.name,customers.customerid,SUM(debit.amount),SUM(credit.amount),
    SUM(COALESCE((debit.amount),0)-COALESCE((credit.amount),0))AS balance FROM
    customers
    RIGHT JOIN credit ON customers.customerid=credit.customer 
    RIGHT JOIN debit ON customers.customerid=debit.customer GROUP BY customers.customerid,debit.year

Query Result

year|   customer |  sum(debit)| sum(credit)| Balance    
2020 |  ac1001   |  48000     | 42000      | 6000
2020 |  ac1002   |  8000      | 6000       | 2000

But what i need is this table below, thank you

Expected Result

year|   customer |  sum(debit)| sum(credit)| Balance    
2019 |  ac1001   |  4000      | 3000       | 1000
2020 |  ac1001   |  4000      | 4000       |  0
2020 |  ac1002   |  4000      | 4000       |  0

Gordon Linoff Gordon Linoff · Accepted Answer · 2020-06-19T00:53:52

union the two tables and then aggregate. You can use a cumulative sum to calculate the balance:

select year, customer, sum(amount) as amount_in_year,
       sum(sum(amount)) over (partition by customer order by year) as end_of_year_balance
from ((select id, year, customer, amount
       from credit
      ) union all
      (select id, year, customer, - amount
       from debit
      ) 
     ) cd
group by year, customer;

EDIT:

For the revised question:

select year, customer, sum(credit) as sum_credit, sum(debit) as sum_debit,
       sum(sum(credit - debit)) over (partition by customer order by year) as end_of_year_balance
from ((select id, year, customer, amount as credit, 0 as debit
       from credit
      ) union all
      (select id, year, customer, 0 as credit, amount as debit
       from debit
      ) 
     ) cd
group by year, customer;

How to get balance from two SQL table debit and credit dynamically

1 Answers