Running Total of Orders — Aggregate Window Functions in SQL

The problem

Brightlane's finance team computes the cumulative revenue total as orders are processed in sequence by ID.

Write a query to return the ID and amount of every order, plus the running total of total_amount accumulated from the first order through that order in order of id.

Assumptions:

The orders table has one row per order with an id and a total_amount.
Orders are processed in ascending id order. The running total at each row is the combined total_amount across every order whose id is less than or equal to that row's id.

Output:

One row per order, with columns id, total_amount, and running_total.

Schema · ecommerce 5 tables

categories

id integer

name text

parent_id? integer

products

id integer

name text

category_id integer

price numeric

stock_qty integer

attributes? jsonb

order_items

id integer

order_id integer

product_id integer

quantity integer

unit_price numeric

customers

id integer

name text

email text

city? text

country text

created_at timestamptz

is_active boolean

orders

id integer

customer_id integer

ordered_at timestamptz

status text

total_amount numeric

Check answerShift Ctrl ↵

Run previews · Check grades

Write a query, then run it to see results here.

Worked solution Try it yourself first

Solution query

SELECT
  id,
  total_amount,
  SUM(total_amount) OVER (
    ORDER BY
      id
  ) AS running_total
FROM
  orders

The shape

SUM(total_amount) OVER (ORDER BY id) attaches a running total to every row. The OVER clause keeps each row in place; the ORDER BY id inside it tells PostgreSQL to accumulate total_amount from the first order through the current one. The cumulative revenue figure rides alongside the order, instead of collapsing the table into a single grand total.

Clause by clause

SELECT id, total_amount returns each order's identifier and amount unchanged. The running total is computed alongside; the original rows are preserved.
SUM(total_amount) OVER (ORDER BY id) AS running_total is the window expression. SUM is the same aggregate used in a GROUP BY, but the OVER clause changes how it is applied. Instead of collapsing rows, it computes a value for each row using a set of related rows. The ORDER BY id inside OVER defines that set as every row with an id less than or equal to the current row, so the result accumulates as id advances. On the first order, running_total is 129.98. On the second, it is 129.98 + 999 = 1128.98. The growth continues to the last row.
FROM orders reads every order. There is no WHERE because the running total spans the whole table.

You practiced SUM(...) OVER (ORDER BY ...) — adding ORDER BY inside the window converts a partition-wide total into a running total accumulating as rows advance.

Return the ID and amount of every order, plus the running total of `total_amount` accumulated from the first order through that order in order of `id`

The shape

Clause by clause

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.