SQL Running Totals with Window Functions

Your finance team wants a report showing cumulative revenue by order date, so they can see how revenue has built up across the month. A standard SUM with GROUP BY gives you one total per date. It doesn't show the running total in SQL that they actually need. That's what SUM() OVER (ORDER BY ...) is for.

How a Running Total Works

A running total accumulates a value row by row as SQL moves through a sorted result. Each row shows the sum of itself and every row before it in the defined order. The last row in the sequence holds the full total.

The key is ORDER BY inside the OVER clause. Without it, SUM() OVER () gives the same grand total on every row. With it, SQL processes rows in sequence and adds each one to the accumulating total.

Basic syntax:

SELECT
    column_name,
    SUM(column_name) OVER (ORDER BY sort_column) AS running_total
FROM table_name;

Here it is against the orders table:

SELECT
    o.order_id,
    o.order_date,
    o.total_amount,
    SUM(o.total_amount) OVER (ORDER BY o.order_date) AS running_revenue
FROM orders o;

Each row shows its own total_amount and the cumulative running_revenue up to that point. The value grows with each row ordered by order_date. By the final row, running_revenue equals the overall total.

Resetting the Running Total per Group

Without PARTITION BY, the running total runs across the entire result. Add PARTITION BY and the total resets at the start of each group.

This query calculates a running total of total_amount within each order status:

SELECT
    o.order_id,
    o.status,
    o.order_date,
    o.total_amount,
    SUM(o.total_amount) OVER (PARTITION BY o.status ORDER BY o.order_date) AS running_by_status
FROM orders o;

Each status group has its own independent accumulation. When SQL moves from the last delivered row to the first pending row, the running total restarts. The logic inside the window doesn't change. Only the scope does.

Running Totals on Other Tables

The pattern works on any numeric column across any table. Here's the same approach on payments, accumulating amount by payment_date:

SELECT
    pay.payment_id,
    pay.payment_date,
    pay.amount,
    SUM(pay.amount) OVER (ORDER BY pay.payment_date) AS cumulative_paid
FROM payments pay;

And here's a running salary total within each department in employees, ordered by hire_date. This shows how payroll committed to each department grew over time as people were hired:

SELECT
    e.employee_id,
    e.department,
    e.hire_date,
    e.salary,
    SUM(e.salary) OVER (PARTITION BY e.department ORDER BY e.hire_date) AS dept_running_salary
FROM employees e;

Each department tracks its own cumulative salary commitment, starting from the earliest hire.

Controlling Display Order Separately

ORDER BY inside OVER only affects how the window function accumulates. If you want the output rows sorted, write a second ORDER BY at the end of the query.

SELECT
    o.order_id,
    o.order_date,
    o.total_amount,
    SUM(o.total_amount) OVER (ORDER BY o.order_date) AS running_revenue
FROM orders o
ORDER BY o.order_date; -- controls display order

Both ORDER BY clauses reference order_date here, but they serve different purposes. The one inside OVER drives accumulation. The one at the end drives the row sequence you see in the output.

How Running Totals Connect to Other Concepts

Running totals use the same OVER clause as other [aggregate window functions like AVG and COUNT](/learn/sql-sum-avg-count-window). The only thing that changes is ORDER BY inside OVER, which is what converts a static aggregate into a cumulative one.

If you need to filter rows based on the running total, for example to return only rows where running_revenue crosses a threshold, wrap the query in a [subquery or CTE](/learn/sql-cte-common-table-expressions). You can't reference a window alias in WHERE at the same query level.

Common Mistakes

1. Omitting ORDER BY inside OVER

Without ORDER BY inside OVER, SQL has no sequence to follow and returns the grand total on every row instead of an accumulating value.

-- wrong: same grand total on every row, not a running total
SELECT
    o.order_id,
    o.order_date,
    o.total_amount,
    SUM(o.total_amount) OVER () AS running_revenue
FROM orders o;

Add ORDER BY inside OVER to tell SQL how to sequence the accumulation.

-- correct
SELECT
    o.order_id,
    o.order_date,
    o.total_amount,
    SUM(o.total_amount) OVER (ORDER BY o.order_date) AS running_revenue
FROM orders o;

2. Filtering on the running total alias in WHERE

Window functions run after WHERE, so the alias doesn't exist when SQL evaluates the filter condition.

-- wrong
SELECT
    pay.payment_id,
    pay.amount,
    SUM(pay.amount) OVER (ORDER BY pay.payment_date) AS cumulative_paid
FROM payments pay
WHERE cumulative_paid > 10000;

Wrap the query in a subquery so the computed column is available to filter on.

-- correct
SELECT * FROM (
    SELECT
        pay.payment_id,
        pay.amount,
        SUM(pay.amount) OVER (ORDER BY pay.payment_date) AS cumulative_paid
    FROM payments pay
) t
WHERE cumulative_paid > 10000;

3. Confusing ORDER BY inside OVER with the final ORDER BY

Using only a final ORDER BY and leaving OVER empty does not create a running total. The display order and the accumulation order are separate concerns.

-- wrong: grand total on each row, just displayed in date order
SELECT
    o.order_id,
    o.order_date,
    o.total_amount,
    SUM(o.total_amount) OVER () AS running_revenue
FROM orders o
ORDER BY o.order_date;

Move the ORDER BY inside OVER to drive the accumulation, and keep the final ORDER BY only for display.

-- correct
SELECT
    o.order_id,
    o.order_date,
    o.total_amount,
    SUM(o.total_amount) OVER (ORDER BY o.order_date) AS running_revenue
FROM orders o
ORDER BY o.order_date;

Quick Recap

• A running total in SQL uses SUM(column) OVER (ORDER BY sort_column) to accumulate a value row by row.
• Without ORDER BY inside OVER, you get a static grand total on every row, not a running total.
• PARTITION BY inside OVER resets the running total at the start of each group.
• ORDER BY inside OVER controls accumulation order. A separate ORDER BY at the end of the query controls display order.
• You can't filter on a window function alias in WHERE. Use a subquery or [CTE](/learn/sql-cte-common-table-expressions) to apply the filter after the calculation.
• The same pattern works on any numeric column: revenue in orders, payments in payments, or salary in employees.

SELECT
    e.employee_id,
    e.hire_date,
    e.salary,
    SUM(e.salary) OVER (ORDER BY e.hire_date) AS running_salary
FROM employees e;

SELECT
    e.employee_id,
    e.department,
    e.hire_date,
    e.salary,
    SUM(e.salary) OVER (PARTITION BY e.department ORDER BY e.hire_date) AS dept_running_salary
FROM employees e;

SELECT
    pay.payment_id,
    pay.method,
    pay.payment_date,
    pay.amount,
    SUM(pay.amount) OVER (PARTITION BY pay.method ORDER BY pay.payment_date) AS running_paid
FROM payments pay;

SELECT
    pay.payment_id,
    pay.method,
    pay.payment_date,
    pay.amount,
    SUM(pay.amount) OVER (PARTITION BY pay.method ORDER BY pay.payment_date) AS running_paid
FROM payments pay
WHERE pay.status = 'completed'
ORDER BY pay.method, pay.payment_date;

SELECT * FROM (
    SELECT
        pay.payment_id,
        pay.payment_date,
        pay.amount,
        SUM(pay.amount) OVER (ORDER BY pay.payment_date) AS cumulative_paid
    FROM payments pay
) t
WHERE cumulative_paid > 5000;