DocsWindow FunctionsRunning Totals

Running Totals

Window Functions

Concept

A running total (cumulative sum) adds up values row by row as you move through an ordered set. In SQL, you create running totals with SUM() as a window function combined with an ORDER BY clause in the OVER frame. The default frame when ORDER BY is present is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which gives you the cumulative sum from the first row up to the current row.

Running totals are fundamental in financial analysis (cumulative revenue, running balances), inventory management (running stock levels), and progress tracking. You can also compute running averages, running counts, and running min/max using the same windowing technique with different aggregate functions.

The frame specification controls exactly which rows contribute to each calculation. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW counts physical rows, while RANGE treats rows with equal ORDER BY values as a group. For most running total use cases, ROWS gives the most intuitive behavior.

Syntax

-- Running total
SELECT
  order_date,
  total_amount,
  SUM(total_amount) OVER (
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM orders;

-- Running average (moving window: last 7 rows)
SELECT
  order_date,
  total_amount,
  AVG(total_amount) OVER (
    ORDER BY order_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS rolling_7_avg
FROM orders;

Practical Example

Using the ecommerce schema (customers, orders, order_items, products)

-- Cumulative revenue by day
SELECT
  order_date,
  daily_revenue,
  SUM(daily_revenue) OVER (
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS cumulative_revenue
FROM (
  SELECT
    order_date,
    SUM(total_amount) AS daily_revenue
  FROM orders
  GROUP BY order_date
) daily
ORDER BY order_date;

-- Running total per customer to track loyalty milestones
SELECT
  customer_id,
  order_date,
  total_amount,
  SUM(total_amount) OVER (
    PARTITION BY customer_id
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS lifetime_spend
FROM orders
ORDER BY customer_id, order_date;

Common Pitfalls & Tips

1RANGE vs ROWS: if multiple rows share the same ORDER BY value, RANGE includes all tied rows in the frame, while ROWS processes them one at a time. This can produce different running totals.
2Forgetting ORDER BY in the OVER clause makes SUM() compute the total of the entire partition for every row — not a running total.
3Ensure your ORDER BY produces a deterministic order. Ties cause the running total to depend on the physical row order, which is non-deterministic.

Practice running totals