By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE transaction_running_balance (
reference INT,
date_ DATE,
bdx_name VARCHAR(32),
bdx_id INT,
description VARCHAR(32),
amount INT
);
INSERT INTO transaction_running_balance
VALUES
(1, '2021-01-01', 'Hello', 33, 'Desc1', 10),
(2, '2021-02-01', 'World', 33, 'Desc2', 20),
(3, '2021-03-01', 'Welcome', 33, 'Desc3', 30),
(4, '2021-04-01', 'Home', 32, 'Desc4', 40);
with temp_table as (
select
reference,
date_,
bdx_name,
bdx_id,
description,
amount,
SUM(amount) OVER (PARTITION BY bdx_id ORDER BY date_) AS cumulativetotal
from transaction_running_balance
where bdx_id = 33
)
select * from temp_table where date(date_) = '2021-03-01';
| reference | date_ | bdx_name | bdx_id | description | amount | cumulativetotal |
|---|---|---|---|---|---|---|
| 3 | 2021-03-01 | Welcome | 33 | Desc3 | 30 | 60 |