The following query returns the cumulative balance per account ordered by transaction date:
SELECT acct_number, trans_date, trans_amount, SUM(trans_amount) OVER (PARTITION BY acct_number ORDER BY trans_date ROWS UNBOUNDED PRECEDING) as balance FROM ledger ORDER BY acct_number, trans_date;
Here are the possible results of the preceding SELECT.
acct_number | trans_date | trans_amount | balance |
---|---|---|---|
73829 | 1998-11-01 | 113.45 | 113.45 |
73829 | 1988-11-05 | -52.01 | 61.44 |
73929 | 1998-11-13 | 36.25 | 97.69 |
82930 | 1998-11-01 | 10.56 | 10.56 |
82930 | 1998-11-21 | 32.55 | 43.11 |
82930 | 1998-11-29 | -5.02 | 38.09 |