How to make a view showing a running transaction balance by account with SQLite-CodePudding

Using SQLite, I want to make a view for an account transaction table that has an additional column, "balance", that shows the balance after that transaction for that account_id. I guess it might be called a running balance by account.

table example:

id |   dateof   | transaction_amount | account_id
 1 | 2022-02-01 |       9500.00      |     1 
 2 | 2022-02-02 |       -500.00      |     1 
 3 | 2022-02-02 |        500.00      |     2
 4 | 2022-02-04 |         10.00      |     2
 5 | 2022-02-05 |         50.00      |     1

view example:

id |   dateof   | transaction_amount | account_id | balance
 1 | 2022-02-01 |       9500.00      |     1      | 9500.00
 2 | 2022-02-02 |       -500.00      |     1      | 9000.00
 3 | 2022-02-02 |        500.00      |     2      |  500.00
 4 | 2022-02-04 |         10.00      |     2      |  510.00
 5 | 2022-02-05 |        -50.00      |     1      | 8950.00

Using the following sql, I was able to create the running balance, but I don't know how to make the balance result based on the account id:

create view acct_txn_v as
with t as (
select * from acct_txn
)
select t.*, sum(amt) over (order by dateof, id) as balance
from t;

CodePudding user response：

You can try to use PARTITION BY account_id in OVER clause of the window function.

A partition consists of all rows that have the same value for all terms of the PARTITION BY clause in the window-defn.

create view acct_txn_v as
with t as (
select * from acct_txn
)
select t.*, sum(amt) over (PARTITION BY account_id  order by dateof, id) as balance
from t;