SQL Cumulative Sums: Frames, Ties, and Opening Balances

A running total needs an order and a frame

A cumulative sum adds values through the current position. “Current” must be defined: one transaction, the end of a date, or the end of a month can all be valid. Repeated dates expose the difference between a row-based frame and PostgreSQL’s default peer-aware frame. This tutorial uses five fictional entries and complete PostgreSQL 16 queries.

Decide these rules first

Choose the output grain, reset key, stable ordering, and frame. Decide whether missing dates need rows and whether the sum begins at zero or an opening balance. Do not partition simply to make a query faster: PARTITION BY changes the mathematical result by resetting it.

Compare transaction and date accumulation

Account A has two entries on January 1, a negative adjustment on January 3, and a February entry. Account B is independent. The following query shows a transaction running total, the default same-date peer total, and a monthly reset side by side:

WITH entries(id,account,posted_on,amount) AS (
 VALUES (1,'A',DATE '2026-01-01',10),
        (2,'A',DATE '2026-01-01',20),
        (3,'A',DATE '2026-01-03',-5),
        (4,'A',DATE '2026-02-01',7),
        (5,'B',DATE '2026-01-02',4)
)
SELECT account,id,posted_on,amount,
 SUM(amount) OVER (
   PARTITION BY account ORDER BY posted_on,id
   ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
 ) AS row_total,
 SUM(amount) OVER (
   PARTITION BY account ORDER BY posted_on
 ) AS date_peer_total,
 SUM(amount) OVER (
   PARTITION BY account,DATE_TRUNC('month',posted_on)
   ORDER BY posted_on,id
   ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
 ) AS month_total
FROM entries ORDER BY account,posted_on,id;

account | id | posted_on | amount | row_total | date_peer_total | month_total
A | 1 | 2026-01-01 | 10 | 10 | 30 | 10
A | 2 | 2026-01-01 | 20 | 30 | 30 | 30
A | 3 | 2026-01-03 | -5 | 25 | 25 | 25
A | 4 | 2026-02-01 | 7 | 32 | 32 | 7
B | 5 | 2026-01-02 | 4 | 4 | 4 | 4

Read the differences in the output

The row_total is 10 after the first January 1 transaction and 30 after the second. The date_peer_total is already 30 for both because they share the ordering date. Neither result is inherently wrong; they answer different questions. The negative entry makes the running total fall to 25. February resets only month_total, and account B never includes A’s entries.

Use a unique tie-breaker for row-by-row output

Ordering by date alone does not determine which same-day transaction comes first. A stable unique ID makes the row-level result deterministic, but its order must still fit the business requirement. If timestamps can tie and IDs do not encode the required sequence, use an authoritative sequence column. The final ORDER BY controls display order separately from the window order.

The frame decides which rows contribute

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW explicitly starts at the first row of the partition and ends at this row. In PostgreSQL, the default frame with an ordering includes peers through the last equal ordering value. See the window-function reference. A finite ROWS frame counts observations, not elapsed days; three rows are not necessarily three calendar months.

Keep history when filtering the display

Suppose A has an opening balance of 100 before the fixture begins, and the report only displays January 3 onward. Compute the running total before the display filter. Otherwise January 1’s 30 disappears from the calculation.

WITH entries(id,account,posted_on,amount) AS (
 VALUES (1,'A',DATE '2026-01-01',10),
        (2,'A',DATE '2026-01-01',20),
        (3,'A',DATE '2026-01-03',-5),
        (4,'A',DATE '2026-02-01',7),
        (5,'B',DATE '2026-01-02',4)
), balances AS (
 SELECT id,account,posted_on,
   100 + SUM(amount) OVER (
     PARTITION BY account ORDER BY posted_on,id
     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
   ) AS balance
 FROM entries WHERE account='A'
)
SELECT id,posted_on,balance FROM balances
WHERE posted_on>=DATE '2026-01-03' ORDER BY posted_on,id;

id | posted_on | balance
3 | 2026-01-03 | 125
4 | 2026-02-01 | 132

The opening 100 is supplied, not inferred from these entries. A real opening balance must correspond to the same account, currency, and cutoff as the included transactions. If all earlier history is available, sum it consistently; if it is not, do not label a partial-period sum as a complete account balance.

Missing dates and unknown amounts need different treatment

There is no A row on January 2. Its absence does not reset the total; it simply creates no output row. A daily report may need a calendar spine joined to daily aggregates. Fill absent activity with zero only if the source is known complete. A null amount on an existing transaction means something else: SUM ignores null inputs, which can hide an unknown amount. Reject, flag, or otherwise resolve it under an explicit data policy.

For inventory, include both receipts and removals with documented signs and a verified opening stock. For revenue, identify refund or reversal timing. For a monthly report, aggregate transactions to one month row before applying a monthly running total; do not call a three-transaction moving frame a three-month measure.

Optimize without changing the result

Inspect sorts and execution plans on representative data. An index matching partition/order keys may help but does not guarantee a particular plan. Removing old rows before the window changes the total unless an equivalent opening balance is carried forward. Splitting into batches also needs an explicit carry-over total for each partition. Late-arriving or corrected transactions can invalidate previously cached totals after their position.

Test ties, resets, and the first displayed row

For the fixture, A ends at 32 and B at 4; A’s monthly total becomes 7 on February 1. Those checks should survive a change in physical row order. If the first displayed A balance becomes 95 instead of 125, the report filtered away required history. Small tests like these are more informative than assuming a running total is correct because it increases smoothly.

FAQ

Why does my sum jump for every row on the same date?

A peer-aware frame can include all rows sharing the date. Use a unique row ordering and an explicit ROWS frame when the requirement is a transaction-by-transaction total.

How do I reset the sum monthly?

Add the account and month to PARTITION BY, while retaining a stable ordering within each partition. A fiscal reset needs the organization’s fiscal-period definition.

Where can I practice more windows?

Continue with the SQL window-functions guide, using explicit grain and frame rules for each problem.