Running Total With a Window Function

A running total is a column that adds up everything so far: today's sales plus every sale before today. Many people build it with a self-join, joining the table to itself on "date less than or equal", and wait for it to finish. A window function does it in one pass and reads like what you mean. This page builds the total, restarts it for each month or customer, and shows the one trap with the default frame.

Step 1: the data

The shop stores prices on products and quantities on order lines, so first get one total per order. A CTE gives the step a name. Cancelled orders are left out.

WITH order_totals AS (
  SELECT o.order_id, o.customer_id, o.order_date, sum(i.quantity * p.price) AS total
  FROM orders AS o
  JOIN order_items AS i ON i.order_id = o.order_id
  JOIN products AS p ON p.product_id = i.product_id
  WHERE o.status <> 'cancelled'
  GROUP BY o.order_id
)
SELECT * FROM order_totals ORDER BY order_date, order_id LIMIT 4;

 order_id | customer_id | order_date | total
----------+-------------+------------+-------
        1 |           1 | 2026-08-01 | 13.25
        3 |           2 | 2026-08-03 | 33.65
        7 |           5 | 2026-08-11 | 24.75
        2 |           1 | 2026-08-15 | 18.00
(4 rows)

Step 2: the running total

sum(total) OVER (ORDER BY ...) adds up the rows from the first to the current one, in that order.

WITH order_totals AS (
  SELECT o.order_id, o.customer_id, o.order_date, sum(i.quantity * p.price) AS total
  FROM orders AS o
  JOIN order_items AS i ON i.order_id = o.order_id
  JOIN products AS p ON p.product_id = i.product_id
  WHERE o.status <> 'cancelled'
  GROUP BY o.order_id
)
SELECT order_id, order_date, total,
       sum(total) OVER (ORDER BY order_date, order_id ROWS UNBOUNDED PRECEDING) AS running_total
FROM order_totals
ORDER BY order_date, order_id
LIMIT 5;

 order_id | order_date | total | running_total
----------+------------+-------+---------------
        1 | 2026-08-01 | 13.25 |         13.25
        3 | 2026-08-03 | 33.65 |         46.90
        7 | 2026-08-11 | 24.75 |         71.65
        2 | 2026-08-15 | 18.00 |         89.65
        5 | 2026-08-20 | 23.97 |        113.62
(5 rows)

The first row shows its own total, the second shows the first two, and so on. The ORDER BY inside OVER sets the order of the adding. The one on the outside only sets the order of the display, and they should agree.

Step 3: restart for each group

PARTITION BY starts the total again for every group. This restarts it each month.

WITH order_totals AS (
  SELECT o.order_id, o.customer_id, o.order_date, sum(i.quantity * p.price) AS total
  FROM orders AS o
  JOIN order_items AS i ON i.order_id = o.order_id
  JOIN products AS p ON p.product_id = i.product_id
  WHERE o.status <> 'cancelled'
  GROUP BY o.order_id
)
SELECT order_id, order_date, total,
       sum(total) OVER (
         PARTITION BY date_trunc('month', order_date)
         ORDER BY order_date, order_id
         ROWS UNBOUNDED PRECEDING
       ) AS month_to_date
FROM order_totals
ORDER BY order_date, order_id
OFFSET 5 LIMIT 5;

 order_id | order_date | total | month_to_date
----------+------------+-------+---------------
        6 | 2026-09-02 | 15.50 |         15.50
        8 | 2026-09-10 | 12.50 |         28.00
        9 | 2026-09-12 | 22.99 |         50.99
       10 | 2026-09-14 | 29.90 |         80.89
       11 | 2026-09-20 | 24.50 |        105.39
(5 rows)

The window resets when the month changes. The first row here is the first order of September, and its total starts again from its own 15.50. Swap the partition for customer_id and you get a running total for each customer.

The trap: ties and the default frame

If you leave out ROWS UNBOUNDED PRECEDING and the ORDER BY value repeats, rows with the same value are added together. That is the default frame, which includes the rows that tie with the current one.

SELECT day, amount,
       sum(amount) OVER (ORDER BY day) AS default_frame,
       sum(amount) OVER (ORDER BY day, amount ROWS UNBOUNDED PRECEDING) AS rows_frame
FROM (VALUES (DATE '2026-09-01', 10), (DATE '2026-09-02', 5), (DATE '2026-09-02', 7)) AS t(day, amount)
ORDER BY day, amount;

    day     | amount | default_frame | rows_frame
------------+--------+---------------+------------
 2026-09-01 |     10 |            10 |         10
 2026-09-02 |      5 |            22 |         15
 2026-09-02 |      7 |            22 |         22
(3 rows)

On 2 September, default_frame shows 22 on both rows. rows_frame adds one row at a time. Which one you want depends on the report, so decide on purpose.

Related course: SQL Intermediate.