Recipe

Running total (cumulative sum)

Short answer: SUM(value) OVER (ORDER BY time_col) accumulates a value across rows in time order. Adding PARTITION BY resets the running total per group (cumulative spend per user, daily cumulative revenue per region). It works on every major engine: window functions are the canonical tool for cumulative metrics.

Canonical SQL #

sql

-- Cumulative total across the whole dataset, ordered by date.
SELECT
  order_date,
  total,
  SUM(total) OVER (ORDER BY order_date) AS running_total
FROM   orders;

-- Cumulative per group: running spend per user, in chronological order.
SELECT
  user_id,
  order_date,
  total,
  SUM(total) OVER (
    PARTITION BY user_id
    ORDER BY order_date
  ) AS lifetime_spend
FROM   orders;

Example #

Loading SQL editor...

Common variants #

sql

-- Rolling 7-day window (not lifetime — just the last 7 rows).
SELECT
  day,
  revenue,
  SUM(revenue) OVER (
    ORDER BY day
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS revenue_7day
FROM daily_revenue;

-- Cumulative count instead of sum.
SELECT
  user_id,
  signed_up_at,
  COUNT(*) OVER (ORDER BY signed_up_at) AS cumulative_signups
FROM users;

-- Cumulative percentage of total.
SELECT
  region,
  revenue,
  SUM(revenue) OVER (ORDER BY revenue DESC) AS cum_revenue,
  SUM(revenue) OVER (ORDER BY revenue DESC) * 100.0 / SUM(revenue) OVER () AS cum_pct
FROM regional_sales;

-- Running min / max / avg — same shape, different aggregate.
SELECT
  day,
  price,
  MIN(price) OVER (ORDER BY day) AS running_low,
  MAX(price) OVER (ORDER BY day) AS running_high,
  AVG(price) OVER (ORDER BY day ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS rolling_30day_avg
FROM stock_prices;

Frames and the default trap #

Window aggregates have an implicit frame, the subset of rows the function operates on. With ORDER BY and no explicit frame, most engines default to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which is exactly what "running total" wants.

The trap: RANGE includes every row tied with the current row's ORDER BY value. If two rows have the same date, both are included in each row's running total, so they get the same running total value. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW walks row-by-row instead, which is usually what you want when your data has duplicates in the order column.

Dialect notes #

Window functions are standard and work the same on Postgres, MySQL (8.0+), SQL Server, Oracle, Snowflake, BigQuery, DuckDB, Redshift, SQLite (3.25+).
MySQL pre-8.0: no window functions. Emulate with a self-join on <= (slow on big tables) or precompute in application code.
RANGE vs ROWS: ROWS counts physical rows; RANGE counts by the ORDER BY value. ROWS is simpler; RANGE matters for time-window calculations (RANGE BETWEEN INTERVAL '7' DAY PRECEDING AND CURRENT ROW, supported on Postgres, Snowflake, Oracle).
Performance: a single window expression scales well; a query with five windows over the same partition often runs in ~5x the time of one (each window is a separate sort/scan unless the planner is clever). Reuse OVER clauses where possible.

Tip

Running total is the gateway to almost every analytics-window pattern. Once you understand SUM(...) OVER (PARTITION BY ... ORDER BY ...), swap SUM for COUNT, AVG, MIN, MAX and you've covered most cumulative metrics. Add a ROWS BETWEEN frame and you get rolling windows. Add LAG / LEAD and you get period-over-period comparisons. Reference: Window functions intro and the related recipe previous row value (LAG).