CodeRang
Common problems

SQL running total with SUM OVER

A SQL running total adds values from the beginning of an ordered sequence through the current row while keeping every source row visible. Learn how SUM OVER, ORDER BY, PARTITION BY, and window frames produce cumulative totals.

Updated · SQL Arena team

Window functions trackAll problems

What you will practice

  • SUM() OVER with deterministic ordering
  • ROWS UNBOUNDED PRECEDING
  • ROWS versus RANGE
  • PARTITION BY for separate totals
  • Moving averages with window frames

A practice problem in the trainer

This is how a problem looks in SQL Arena: the task, the editor, the result and the database schema.

PrevNextRandom
TaskSubmissionsAI Mentor
Calculate a store's cumulative sales

The daily_sales table contains individual store sales. For store_id = 1, return the sale date as sale_date, the current row amount as amount, and the cumulative amount as running_total. Multiple sales on the same date must be accumulated one by one. Sort the result by sale date and sale id.

Query 1PostgreSQLv16
SELECT sale_date,
       amount,
       SUM(amount) OVER (
         PARTITION BY store_id
         ORDER BY sale_date, id ROWS UNBOUNDED PRECEDING
       ) AS running_total
FROM daily_sales
WHERE store_id = 1
ORDER BY sale_date, id;
Submit
Resultstoresdaily_sales
Answer accepted

How to calculate a SQL running total with SUM OVER

SUM(amount) becomes a window calculation because it has an OVER clause. Unlike a grouped SUM, it does not collapse the five sales into one result row. Each sale remains visible, which lets you compare the transaction amount with the cumulative value after that transaction.

PARTITION BY store_id creates an independent window for each store. The example filters to one store, but keeping the partition in the expression makes the rule explicit. If the filter is removed later, each store starts its own cumulative sum instead of inheriting the previous store's total.

ORDER BY sale_date, id is important because two sales happened on October 2. Ordering only by the date leaves those peer rows without a unique sequence. ROWS UNBOUNDED PRECEDING then tells PostgreSQL to sum every physical row from the start of the partition through the current row, producing 120, 200, 250, 390, and 450.

A SQL running total, cumulative sum, and cumulative total usually describe the same pattern: each row receives the sum of values from the start of a sequence through its current position. SUM becomes a window function when used with OVER, while ORDER BY controls the accumulation sequence. PARTITION BY starts a separate total for each group. For explicit row-by-row behavior, ROWS UNBOUNDED PRECEDING defines a frame from the first row of the partition through the current row. A tie-breaking column in ORDER BY can make the sequence deterministic when sort values repeat.

SQLPostgreSQL v16
SELECT sale_date,
       amount,
       SUM(amount) OVER (
         PARTITION BY store_id
         ORDER BY sale_date, id ROWS UNBOUNDED PRECEDING
       ) AS running_total
FROM daily_sales
WHERE store_id = 1
ORDER BY sale_date, id;
Query result
sale_dateamountrunning_total
2026-10-01120120
2026-10-0280200
2026-10-0250250
2026-10-03140390
2026-10-0460450

Common SQL running total mistakes

01

Using a non-unique window order

A date is often not enough to define transaction order because several records may share it. Add a stable tie-breaker such as an id when the business meaning requires one row to be processed before another.

02

Ignoring the ROWS versus RANGE difference

In PostgreSQL, an ordered window without an explicit frame can behave like RANGE through the current peer group. Rows sharing the same ORDER BY value may therefore receive the same cumulative amount. ROWS is the clearer choice when the total must advance record by record.

03

Forgetting to partition the total

Without PARTITION BY, the first record of the next customer, store, account, or category continues from the previous group's total. The SQL still runs, so this error can survive until someone checks the business logic.

Practice problems on this topic

Window functions track

Once the basic pattern is clear, practice it on data where the frame is less obvious. SQL Arena includes problems around cumulative game activity, running city sales, stock changes over time, and session-based event analysis. The “Window Functions from Start to Finish” track groups this kind of practice into a focused progression.

Reference on this topic

In-depth articles with syntax and examples in the SQL Arena reference.

Frequently asked questions

How do I calculate a SQL running total?

Use SUM(column) as a window function with OVER and an ORDER BY clause. The ordering determines the sequence in which the total accumulates.

How do I calculate a cumulative sum in SQL?

A common pattern is SUM(value) OVER (ORDER BY ... ROWS UNBOUNDED PRECEDING). It returns a cumulative value while preserving individual rows.

What does PARTITION BY do in a SQL running total?

PARTITION BY creates independent groups for the calculation. The running total restarts from the beginning for every partition.

Why use ROWS UNBOUNDED PRECEDING for a running total?

It explicitly sets the window frame from the first row of the partition to the current row. This makes row-by-row cumulative behavior clear.

See also

Practice SQL running total queries

SQL Arena has 1,192 auto-checked tasks, including window-function problems that go beyond a single textbook pattern. Solutions are checked against hidden data. PostgreSQL, MySQL, ClickHouse, and BigQuery each have their own browser sandbox, and 292 tasks are available on the free plan without a card.

Practice SQL window functions

More practice from CodeRang