Skip to content
Expert O'clock

SQL

SQL window functions for running totals

How to calculate running totals with SUM OVER, choose the right window frame, reset by partition, and check ties and gaps.

Talha Ansari··5 min read

Key takeaways

  • A window function keeps detail rows while adding a calculation across them.
  • An ORDER BY inside OVER creates a cumulative result by default.
  • Use an explicit ROWS frame when ties must not change the running total.
  • Partition by the entity and period that should reset the total.
On this page

What a window function adds

GROUP BY collapses rows into one row per group. A window function calculates across a set of related rows and returns a value on every row. That difference matters for running totals, because the report usually needs both the monthly figure and the cumulative figure on the same line.

The OVER clause defines the window: which rows to include, how to order them, and whether to restart the calculation for each group. Getting those three parts right is most of the work.

Build a running total

Start with a monthly aggregate, then apply SUM with an ORDER BY inside OVER. Without a frame clause, adding ORDER BY makes the sum cumulative: each row includes all earlier rows in the window.

The query below produces monthly revenue, a running total, the previous month, and the change. The CTE keeps the aggregation separate from the window calculation, which makes both easier to read and test.

Monthly revenue with a running total and prior-month comparisonSQL
WITH monthly AS (
  SELECT
    DATE_TRUNC('month', order_date)::date AS month,
    SUM(order_total) AS revenue
  FROM orders
  GROUP BY DATE_TRUNC('month', order_date)
)
SELECT
  month,
  revenue,
  SUM(revenue) OVER (
    ORDER BY month
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_revenue,
  LAG(revenue) OVER (ORDER BY month) AS previous_month,
  revenue - LAG(revenue) OVER (ORDER BY month) AS change
FROM monthly
ORDER BY month;

Make the frame explicit

With ORDER BY and no frame, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. RANGE includes all rows that share the current ORDER BY value. If two rows have the same month or the same timestamp, both are included at once and the running total jumps.

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW counts rows one at a time. That is usually what a running total means. Add a unique tiebreaker to the ORDER BY, such as an entry ID, so the sequence is deterministic.

  • Use ROWS for a row-by-row cumulative total.
  • Use RANGE when peers should be treated as one point in time.
  • Add a unique column to ORDER BY when ties are possible.
  • Never assume the database returns tied rows in insertion order.

Reset the total with partitions

PARTITION BY restarts the calculation for each group. Partition by account to get a running balance per account, or by year to get a year-to-date total that resets each January.

The partition and the ORDER BY answer different questions. The partition says where the calculation starts again; the order says how rows accumulate within it. A running total partitioned by customer but ordered by date gives each customer their own timeline.

Parts of a window definition
ClauseWhat it controlsTypical use
PARTITION BYWhere the calculation restartsRunning balance per account
ORDER BYThe sequence within each partitionCumulative revenue by month
ROWS or RANGEWhich rows the frame includesYear-to-date versus full-period total
WINDOW clauseNames a reusable window definitionSeveral columns sharing one window

Rank and deduplicate with care

ROW_NUMBER gives every row a unique number, even when rows tie. RANK gives tied rows the same number and leaves gaps in the sequence. DENSE_RANK gives tied rows the same number without gaps. The choice changes which record a deduplication query keeps.

A common pattern numbers rows within a customer by recency and keeps row one. Add deterministic tiebreakers so the same record wins on every run. Without them, the result can change between refreshes even when the data has not changed.

Check the result before publishing

The final running total should equal the sum of all rows in the partition. Compare the last value with a plain SUM to catch a frame error. Check that the first row starts at its own value, not zero, unless a zero start is intended.

Look for missing months. A running total built on incomplete periods is still arithmetically correct but tells the wrong story. Join to a calendar table or generate the period list so gaps are visible.

  • Compare the final running total with a total aggregate.
  • Check the first and last row of every partition.
  • Confirm that each partition has the expected number of periods.
  • Test with duplicate dates and with a single-row partition.

Frequently asked questions

What is the difference between GROUP BY and a window function?+

GROUP BY returns one row per group. A window function returns a value for every row while still seeing the other rows in its window.

Why is a running total different from a simple cumulative sum?+

The frame or the ORDER BY probably treats ties differently. Use an explicit ROWS frame and a unique tiebreaker.

Can a window function be filtered in WHERE?+

No. Window functions are evaluated after WHERE, so filter in a CTE or subquery and apply the condition outside it.

Does PARTITION BY change the number of rows?+

No. It changes how the calculation is grouped, not the row count. Only GROUP BY or DISTINCT reduce rows.

Need help applying this?

Improve your data cleanup & pipelines.

Review the service scope or tell us what you are trying to improve. We will reply with a clear next step.

Discuss this serviceView service
Free KPI definition template

Related reading