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;
>Solution :
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;