Filtered running sum view in SQLite

May 9, 2023

I have an append-only Operation table with the following columns:

Id, INTEGER, primary key, auto-incremented
LotId, INTEGER, foreign key, auto-incremented
Amount, INTEGER

Whenever there is a new row inserted in this table I need the balance to be reflected, so I created a Ledger view with:

CREATE VIEW IF NOT EXISTS "Ledger" ("OperationId", "Balance") 
AS SELECT "Id", SUM("Amount") OVER (ROWS UNBOUNDED PRECEDING) 
FROM "Operation";

What I need is further filtering of the running sum by LotId, that is the sum should only include those rows from Operation where the LotId matches.

Example

Operation                  Ledger

Id | LotId |Amount         OperationId | LotId | Balance
---+-------+------         ------------+-------+--------
1  | 1     | 10            1           | 1     | 10
2  | 2     | 5             2           | 2     | 5
3  | 1     | -7            3           | 1     | 3

>Solution :

You should use the PARTITION BY clause to get the sums by LotId:

CREATE VIEW IF NOT EXISTS Ledger AS 
SELECT Id AS OperationId, 
       LotId,
       SUM(Amount) OVER (PARTITION BY LotId ORDER BY Id) AS Balance
FROM Operation;

See the demo.