SQL Running Totals: Add Values Without Losing the Detail

A running total shows how a cumulative amount grows as you move through a defined sequence. It keeps every detail row and adds a summary beside it. The calculation is meaningful only when you specify what sequence you intend.

Practice setup: Use the fictional database in the SQL practice lab. Run that setup once in a fresh SQLite practice database. Customers are Amina (1, Nairobi), Brian (2, Mombasa) and Chao (3, Nairobi). Orders are 101 (customer 1, amount 1200), 102 (customer 2, amount 800), 103 (customer 1, amount 600) and 104 (customer 3, amount 1500). Amounts are fictional units. The examples in this series use SQLite syntax; check your database's documentation before adapting them elsewhere.

Define the order before adding

Our practice table has order IDs but no order timestamps. We can demonstrate a running total in order_id sequence, but should not describe it as a true chronological sales history without a date field.

SELECT order_id, amount,
       SUM(amount) OVER (
         ORDER BY order_id
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_amount
FROM orders
ORDER BY order_id;

The running amounts are 1200, 2000, 2600 and 4100. The frame starts at the first row and ends at the current row. Writing ROWS explicitly avoids relying on a default frame whose treatment of equal sort values may be different from what you expect.

Check the final row against the grand total

The last running amount should equal SELECT SUM(amount) FROM orders;, which is 4100. The first should equal the first included amount, 1200. These two checks catch many range and ordering mistakes, though they do not replace inspecting the intermediate rows.

SUM ignores NULL amounts. If a real dataset allows them, decide whether a missing amount should leave the cumulative total incomplete or be flagged separately. A running total cannot repair unknown input.

Restart the total for each customer

SELECT order_id, customer_id, amount,
       SUM(amount) OVER (
         PARTITION BY customer_id
         ORDER BY order_id
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS customer_running_amount
FROM orders
ORDER BY customer_id, order_id;

Amina's two order rows show 1200 and then 1800. Brian shows 800 and Chao shows 1500. PARTITION BY restarts the calculation separately for each customer while preserving their detail records.

Understand where a filter acts

WHERE filters the input before the window calculation in the same SELECT. If you use WHERE amount >= 1000, the running total includes only orders 101 and 104 and finishes at 2700. It is not the full cumulative total with smaller rows merely hidden.

If you need to calculate on all rows and then display a subset, put the window query inside a CTE and filter in the outer SELECT. Explain this choice in the report, because both results can look reasonable while answering different questions.

Avoid ambiguous sequences

When ordering by a non-unique date or amount, add a stable tie-breaker if the calculation needs one row at a time. A business may instead want a daily total shared by all same-day rows; that is a different definition. Agree on the meaning before choosing a frame.

Try it yourself

Calculate a running amount in order_id order using only orders worth at least 800. Return order_id, amount and running_amount. What should the final value be?

SELECT order_id, amount,
       SUM(amount) OVER (
         ORDER BY order_id
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_amount
FROM orders
WHERE amount >= 800
ORDER BY order_id;

Keep exploring: SQL learning path.