SQL Window Functions Explained

A window function calculates a value across a set of rows related to the current row — without collapsing those rows into a single output row the way GROUP BY does. You get the aggregate, and you still get every individual row.

That's the key difference: GROUP BY turns many rows into one summary row per group, while a window function attaches a calculated value to each row, letting you compare an individual row against the group it belongs to. This is exactly what you need for things like "show me each employee's salary alongside their department's average" in a single result set.

The OVER() Clause

Every window function is paired with an OVER() clause, which defines the "window" of rows the calculation should consider for each row. The PARTITION BY inside it works like a grouping key, but instead of collapsing rows, it just scopes the calculation.

SELECT
    name,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg_salary
FROM employees;

Result:

name | department | salary | dept_avg_salary
--------|-------------|--------|----------------
Alice | Engineering | 82000 | 76000
Bob | Engineering | 70000 | 76000
Priya | Sales | 61000 | 63500
Diego | Sales | 66000 | 63500

Notice that every row is still present — Alice and Bob both see the same Engineering average (76000) next to their own individual salary, because PARTITION BY department scopes the average separately for each department without merging the rows together.

A Running Total Example

Window functions can also respect row order within a partition using ORDER BY inside OVER() — useful for running totals:

SELECT
    name,
    hire_date,
    salary,
    SUM(salary) OVER (ORDER BY hire_date) AS running_total
FROM employees
WHERE department = 'Engineering';

Result:

name | hire_date | salary | running_total
--------|------------|--------|---------------
Bob | 2022-03-10 | 70000 | 70000
Alice | 2023-07-01 | 82000 | 152000

Each row's running_total is the sum of its own salary plus every row before it in hire-date order — something GROUP BY simply can't express, since it would only ever give you one final total.

Common Window Functions

Beyond AVG() and SUM(), almost any aggregate function (COUNT(), MIN(), MAX()) works as a window function the moment you add OVER(). There's also a dedicated family of ranking window functions — RANK(), DENSE_RANK(), and ROW_NUMBER() — purpose-built for numbering rows within a partition, which is one of the most common real-world uses of this feature.

Window functions are supported in PostgreSQL, SQL Server, and MySQL 8.0+; older MySQL versions (5.7 and earlier) don't support them at all.