SUM, AVG, COUNT as SQL Window Functions

A finance analyst pulls up the orders table and needs two things at once: the value of each individual order and the running total of revenue across all orders by date. A GROUP BY query can't give both. That's where SUM, AVG, and COUNT as window functions come in.

How Aggregate Window Functions Work

When you use SUM(), AVG(), or COUNT() normally with GROUP BY, SQL collapses multiple rows into one. You lose the detail. Window functions do the calculation but keep every row intact. The result appears as an extra column alongside your existing data.

The structure is the same for all three:

SELECT
    column_name,
    SUM(column_name) OVER (PARTITION BY group_column ORDER BY sort_column) AS alias
FROM table_name;

The OVER clause is what makes it a window function. PARTITION BY splits the calculation into groups. ORDER BY inside OVER controls the order SQL processes rows, which matters for running totals. Both are optional depending on what you need.

Here's a concrete example. This query returns every order alongside the total revenue across all orders:

SELECT
    o.order_id,
    o.order_date,
    o.total_amount,
    SUM(o.total_amount) OVER () AS overall_total
FROM orders o;

The empty OVER () means no partitioning and no ordering. Every row gets the same grand total. It's not a running total yet. Just a global sum repeated on each row.

Running Totals with ORDER BY

Adding ORDER BY inside OVER changes what SUM calculates. Instead of summing all rows at once, SQL accumulates the total as it moves through the sorted result. Each row gets the sum of all rows up to and including itself.

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

Row by row, the running_total grows. By the last row in date order, it equals the overall total. This is the most common use of SUM as a window function.

Using PARTITION BY to Reset the Calculation

PARTITION BY breaks the calculation into independent groups. The sum, average, or count resets for each group. The rows still all appear. Only the scope of the calculation changes.

This query shows each order's total_amount alongside the total revenue per status group:

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

Every delivered order shows the total for delivered orders. Every pending order shows the total for pending orders. No GROUP BY needed, and no rows disappear.

You can combine PARTITION BY and ORDER BY to get a running total that resets within each group:

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;

AVG and COUNT Work the Same Way

The syntax is identical. Swap SUM for AVG or COUNT and the logic carries over directly.

AVG with PARTITION BY attaches each employee's department average salary to every row:

SELECT
    e.employee_id,
    e.department,
    e.salary,
    AVG(e.salary) OVER (PARTITION BY e.department) AS dept_avg_salary
FROM employees e;

This lets you compare each employee's salary to their department average in one query, without a subquery or a join.

COUNT with PARTITION BY tells you how many rows exist in each group, attached to every row in that group:

SELECT
    e.employee_id,
    e.department,
    COUNT(*) OVER (PARTITION BY e.department) AS dept_headcount
FROM employees e;

Every row in the Engineering department, for example, shows the same headcount number.

How This Connects to Other Concepts

These three functions build on the same OVER clause used by ranking functions like [RANK() and ROW_NUMBER()](/learn/sql-rank-window-function). If you're comfortable with those, the syntax here is the same. The only difference is what the function calculates.

If you need to filter on a window function result, wrap the query in a [subquery or CTE](/learn/sql-cte-common-table-expressions). You can't reference a window function alias in a WHERE clause in the same query level.

Common Mistakes

1. Expecting GROUP BY behavior from a window function

A window function does not collapse rows. If you want one row per group with a total, use GROUP BY instead.

-- wrong expectation: one row per status with total
SELECT
    o.status,
    SUM(o.total_amount) OVER (PARTITION BY o.status) AS status_total
FROM orders o;

Use GROUP BY when you want aggregated rows, not window functions.

-- correct: one row per status
SELECT
    o.status,
    SUM(o.total_amount) AS status_total
FROM orders o
GROUP BY o.status;

2. Confusing ORDER BY inside OVER with ORDER BY at the end

ORDER BY inside OVER controls how the window function accumulates. ORDER BY at the end of the query controls display order. They do different things.

-- wrong: no ORDER BY in OVER means no running total, just global sum
SELECT
    o.order_id,
    o.order_date,
    o.total_amount,
    SUM(o.total_amount) OVER () AS running_total
FROM orders o
ORDER BY o.order_date;

Move ORDER BY inside the OVER clause to get a true running total.

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

3. Filtering on a window function alias in WHERE

Window functions run after WHERE, so you can't use their alias to filter in the same query level.

-- wrong
SELECT
    e.employee_id,
    e.salary,
    AVG(e.salary) OVER (PARTITION BY e.department) AS dept_avg
FROM employees e
WHERE dept_avg > 60000;

Wrap the query so the window result becomes a column you can filter on.

-- correct
SELECT * FROM (
    SELECT
        e.employee_id,
        e.salary,
        AVG(e.salary) OVER (PARTITION BY e.department) AS dept_avg
    FROM employees e
) t
WHERE dept_avg > 60000;

Quick Recap

• SUM(), AVG(), and COUNT() used with OVER are window functions that add a calculated column without removing any rows.
• PARTITION BY inside OVER resets the calculation for each group, similar to how GROUP BY defines groups.
• ORDER BY inside OVER makes SUM accumulate row by row, producing a running total.
• AVG() OVER (PARTITION BY ...) lets you compare each row's value to its group average in one query.
• You cannot filter on a window function result in WHERE. Use a subquery or [CTE](/learn/sql-cte-common-table-expressions) to filter after the calculation.
• When you need one row per group with a total, use GROUP BY, not a window function.

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

SELECT
    e.employee_id,
    e.department,
    e.salary,
    AVG(e.salary) OVER (PARTITION BY e.department) AS dept_avg
FROM employees e;

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

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

SELECT * FROM (
    SELECT
        e.employee_id,
        e.department,
        e.salary,
        AVG(e.salary) OVER (PARTITION BY e.department) AS dept_avg
    FROM employees e
) t
WHERE salary > dept_avg;