Running Totals with SUM() OVER

A running total (also called a cumulative sum) adds up values row by row in order, so each row shows the total accumulated up to that point. It's one of the most frequently requested analytical patterns in SQL — and window functions make it clean and fast.

The simplest running total

-- Cumulative revenue from completed orders, ordered by date
WITH daily AS (
    SELECT
        o.created_at::DATE                           AS created_at,
        SUM(oi.quantity * oi.unit_price)             AS daily_revenue
    FROM orders o
    JOIN order_items oi ON oi.order_id = o.order_id
    WHERE o.status = 'completed'
    GROUP BY o.created_at::DATE
)
SELECT
    created_at,
    daily_revenue,
    SUM(daily_revenue) OVER (ORDER BY created_at)   AS running_total
FROM daily
ORDER BY created_at;

SUM(daily_revenue) OVER (ORDER BY created_at) — because ORDER BY is present in OVER, the default frame is ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. This means: sum everything from the first row up to and including the current row.

What the default frame actually does

When you write SUM(x) OVER (ORDER BY y), PostgreSQL applies this frame automatically:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

This is subtly different from ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. With RANGE, rows that tie on the ORDER BY column are all included up to the last tied row — meaning tied rows all show the same running total (the total through the last tied row, not partial).

For running totals where ties exist (e.g., multiple orders on the same date), you usually want explicit ROWS BETWEEN:

SUM(daily_revenue) OVER (
    ORDER BY created_at
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

Frame clauses are covered in depth in a dedicated lesson. For now: when in doubt about ties, use ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW explicitly.