how to get balance per recored in sqlite

November 29, 2022

I have table called transaction_table so I write the this code

select 
    t._id, 
    t.description, 
    t.debit, 
    t.credit,
    (t.debit - t.credit) blnc,
    t.curr_id, 
    t.cus_id 
from transaction_table t

the result:

                                               this col
_id  description             debit    credit     blnc   curr_id   cuss_id
-------------------------------------------------------------------------
1    cr for customer 1       0.0     30.0      -30.0    1         1
2    cr for customer 1       0.0    500.0     -500.0    1         1
3    dbt for customer 1     70.0      0.0       70.0    1         1
4    cr for customer 2       0.0    600.0     -600.0    1         2
5    dat for customer 2    100.0      0.0      100.0    1         2
6    dat for customer 2    300.0      0.0      300.0    1         2

but I want some thing like this:

_id  description             debit    credit     blnc   curr_id   cuss_id
-------------------------------------------------------------------------
1    cr for customer 1       0.0     30.0      -30.0    1         1
2    cr for customer 1       0.0    500.0     -530.0    1         1
3    dbt for customer1      70.0      0.0      470.0    1         1
4    cr for customer 2       0.0    600.0     -600.0    1         2
5    dat for customer 2    100.0      0.0     -500.0    1         2
6    dat for customer 2    300.0      0.0     -200.0    1         2

So, how to achieve the second table using sqlite? thank you.

>Solution :

Use SUM() window function:

SELECT _id, description, debit, credit,
       SUM(debit - credit) OVER (PARTITION BY curr_id, cus_id ORDER BY _id) blnc,
       curr_id, cus_id 
FROM transaction_table;

Depending on your requirement you may remove the column curr_id from the PARTITION BY clause.

For versions of SQLite prior to 3.25, that do not support window functions, use a self join and aggregation:

SELECT t1._id, t1.description, t1.debit, t1.credit,
       SUM(t2.debit - t2.credit) blnc,
       t1.curr_id, t1.cus_id
FROM transaction_table t1 INNER JOIN transaction_table t2
ON t2.curr_id = t1.curr_id AND t2.cus_id = t1.cus_id AND t2._id <= t1._id
GROUP BY t1._id;

Depending on your requirement you may remove the condition t2.curr_id = t1.curr_id from the ON clause.

See the demo.