Window Functions

Aggregates with GROUP BY collapse rows into one per group. Window functions calculate across a set of related rows without collapsing them — each row keeps its identity and gains a value computed from its "window": a department average beside each salary, a running total, the previous order's date.

The OVER clause

SELECT first_name, department_id, salary,
       round(AVG(salary) OVER ()) AS avg_of_result
FROM employees
WHERE department_id IN (1, 2);

Adding OVER (…) turns an aggregate into a window function. With an empty OVER (), the window is the whole result, so every row shows the same average — but all the rows are still there. Because window functions run after WHERE, it's the average of the Sales and Engineering rows shown, not of the whole company.

PARTITION BY: windows per group

SELECT first_name, department_id, salary,
       round(AVG(salary) OVER (PARTITION BY department_id)) AS dept_avg,
       round(salary - AVG(salary) OVER (PARTITION BY department_id)) AS vs_dept
FROM employees
WHERE department_id IN (1, 2)
ORDER BY department_id, salary DESC;

PARTITION BY splits rows into groups, like GROUP BY, but the function's result is attached to every row of the group. Comparing each value with its group's average is now a single, readable query.

Share of a total

SELECT category, name, round(price * stock, 2) AS stock_value,
       round(100.0 * price * stock / SUM(price * stock) OVER (PARTITION BY category), 1) AS pct_of_category
FROM products
WHERE category IN ('Home', 'Toys')
ORDER BY category, pct_of_category DESC;

ORDER BY inside OVER: running totals

SELECT id, order_date,
       COUNT(*) OVER (ORDER BY order_date) AS orders_so_far
FROM orders
WHERE order_date < '2025-12-31';

Adding ORDER BY inside OVER makes the window grow row by row: each row sees itself and every row before it. That turns COUNT and SUM into running totals.

Where window functions can go

Window functions are calculated after WHERE, GROUP BY and HAVING, so they can appear only in the select list and ORDER BY. To filter on one, wrap the query in a CTE or subquery:

WITH ranked AS (
  SELECT first_name, department_id, salary,
         AVG(salary) OVER (PARTITION BY department_id) AS dept_avg
  FROM employees
)
SELECT first_name, department_id, salary, round(dept_avg) AS dept_avg
FROM ranked
WHERE salary > dept_avg;

Windows over grouped results

SELECT strftime('%Y-%m', o.order_date) AS month,
       round(SUM(oi.quantity * oi.unit_price), 2) AS revenue,
       round(SUM(SUM(oi.quantity * oi.unit_price)) OVER (ORDER BY strftime('%Y-%m', o.order_date)), 2) AS cumulative
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.id
WHERE o.status <> 'cancelled'
GROUP BY month
ORDER BY month;

The SUM(SUM(…)) OVER (…) looks strange but is logical: the inner SUM is the monthly aggregate, and the outer window SUM accumulates those monthly totals.

Named windows

SELECT first_name, salary,
       MIN(salary) OVER w AS dept_min,
       MAX(salary) OVER w AS dept_max
FROM employees
WHERE department_id = 1
WINDOW w AS (PARTITION BY department_id);

The WINDOW clause defines a window once for several functions. It's supported by PostgreSQL, MySQL 8, SQLite and SQL Server 2022.

Try it yourself

Show each product with its category's cheapest and most expensive price.