Cumulative vs Grand Total

Concepts covered: Aggregate Window Fns, Window Frames, Scalar Subquery, Sums & Averages

These 2 requests sound alike and want different columns. "Show each transaction with the balance after it" wants a number that changes down the page. "Show each transaction with the month's net change beside it" wants the same number, 250, on all 5 rows, so a reader can see how large each movement is against the month. Both are SUM(amount) OVER, both keep 5 rows, and the only difference in the SQL is whether ORDER BY txn_date appears inside the OVER. With it the frame grows row by row; without it the frame is the whole table every time, and the whole table summed is the grand total.

-- Both columns in 1 SELECT: the constant and the one that grows
SELECT
  txn_date,
  amount,
  SUM(amount) OVER () AS grand_total,
  SUM(amount) OVER (
    ORDER BY txn_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS balance_so_far
FROM transactions
ORDER BY txn_date;

SELECT txn_date, amount,
  SUM(amount) OVER () AS grand_total,
  SUM(amount) OVER (
    ORDER BY txn_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS balance_so_far
FROM transactions

Watch the 2 columns fill. grand_total reads 250 on every row because its window has no ordering and so no notion of earlier, and the frame it gathers is all 5 rows each time. balance_so_far reads 500, 380, 630, 550, 250, growing with each row's frame. The 2 columns meet on the last row, where the total so far has caught up with everything, and that meeting point is a property you can state in the room: the final value of a running total always equals the grand total of the same rows.

Hearing which one the question wants

Words that want the grand total

• "beside each row", "next to each gift"
• "the month's total", "the campaign total", "overall"
• "compared with the whole period"
• "what share of the total is this row"

Words that want the total so far

• "after each", "following each"
• "so far", "to date", "as of"
• "accumulated since", "since launch"
• "the first day it passed 10,000"

The test is whether the number should change as you read down the rows. A request that describes a moment, after this row, as of this date, so far, wants a figure that moves, and that figure needs an ordering. A request that describes the whole period, the month's total, the campaign total, the overall figure, wants 1 number repeated, and an ordering would be a mistake because it turns the constant into a staircase. Ask when the wording leaves it open: "Do you want the balance as of each row, or this month's total shown beside each row?" The question costs 5 seconds and prevents the wrong column.

The grand total beside each row without a join

-- The pre-window way: a scalar subquery evaluated once, copied onto every row
SELECT
  txn_date,
  amount,
  (SELECT SUM(amount) FROM transactions) AS grand_total
FROM transactions
ORDER BY txn_date;

Before window functions the grand total beside each row came from a scalar subquery or a join back to a GROUP BY result, and both still work. The subquery runs once and its single value is copied onto each row; the join needs a grouping key and loses rows if any key fails to match. SUM(amount) OVER () does the same work in 1 pass with no second scan and no join condition to get wrong, and it sits in the same SELECT as the growing column, as the earlier code shows. In the room, write the window form and offer the subquery form if asked for an alternative; naming both shows you know what the window replaced.

The follow-up that usually comes next divides the growing column by the constant one to get the share of the month reached after each row, and the intermediate lesson opens with it; everything that query needs is already on the row.

About this lesson

This section is part of the Running Totals: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.