how to get balance sheet (debit , credit , balance) from transactions table in SQL?

user_18347738 0
Viewed 41

if I have transactions table like that:

+----+--------+------------+-------------+--------+
| id | userID | debitAccID | creditAccID | amount |
+----+--------+------------+-------------+--------+
|  1 |      1 |          1 |           2 |    500 |
|  2 |      1 |          1 |           3 |    600 |
|  3 |      1 |          3 |           1 |    200 |
+----+--------+------------+-------------+--------+

how what query to use to get a table for account with id 1 like that:

+----+--------+------------+-------------+--------+
|   debit      | credit      |balance |
+----+--------+------------+-------------+--------+
| |        500 |             |    500 |
| |        600 |             |   1100 |
| |            |          200|    900 |
+----+--------+------------+-------------+--------+
                                  900
sql
1 Answers

Assuming the id column shows the correct order of transactions, you can use case and window with the default of rows between unlimited preceding and current row to get your output:

select id, user_id, 
       case when user_id = debit_acc_id then amount else 0 end as debit,
       case when user_id = credit_acc_id then amount else 0 end as credit,
       sum(case when user_id = debit_acc_id then amount else 0 end) over w 
       - sum(case when user_id = credit_acc_id then amount else 0 end) over w as balance
  from transactions
 where user_id = 1
window w as (partition by user_id order by id)
 order by user_id, id;

db<>fiddle here

user_138083190
Related