What you will be able to do
After this lesson you can put a group-level number, such as the department average, right beside each individual row, without losing any rows. That is what window functions are for.
The problem GROUP BY cannot solve
GROUP BY answers "what is the average salary in each department?" by squeezing every department into a single row. That is perfect for a summary, but it throws the individual employees away. Suppose you want each employee and their department's average on the same line, so you can see who is above or below it. With GROUP BY alone that is awkward. A window function does it directly.
A window function looks like an aggregate followed by OVER (...). The OVER clause describes the window: the set of rows the function looks at when it works out the value for the current row. Every row stays in the result.
The simplest window: all rows
An empty OVER () means "the window is the whole result".
SELECT first_name, last_name, salary,
ROUND(AVG(salary) OVER (), 0) AS company_average
FROM employees
ORDER BY salary DESC
LIMIT 4;+------------+-----------+--------+-----------------+
| first_name | last_name | salary | company_average |
+------------+-----------+--------+-----------------+
| Meera | Iyer | 180000 | 90050.0 |
| Vikram | Singh | 150000 | 90050.0 |
| Arjun | Rao | 125000 | 90050.0 |
| Kavya | Nair | 125000 | 90050.0 |
+------------+-----------+--------+-----------------+The company average, 90,050, is calculated once over all 20 employees and repeated on each row. (SQLite shows ROUND results with a trailing .0; other databases may format the number slightly differently, but the value is the same.)
PARTITION BY: one window per group
PARTITION BY splits the rows into groups, like GROUP BY, but without collapsing them. The function is calculated separately inside each group.
SELECT first_name, department_id, salary,
ROUND(AVG(salary) OVER (PARTITION BY department_id), 0) AS dept_average,
COUNT(*) OVER (PARTITION BY department_id) AS dept_size
FROM employees
WHERE department_id IN (1, 3)
ORDER BY department_id, salary DESC;+------------+---------------+--------+--------------+-----------+
| first_name | department_id | salary | dept_average | dept_size |
+------------+---------------+--------+--------------+-----------+
| Meera | 1 | 180000 | 108333.0 | 6 |
| Arjun | 1 | 125000 | 108333.0 | 6 |
| Kavya | 1 | 125000 | 108333.0 | 6 |
| Rohan | 1 | 82000 | 108333.0 | 6 |
| Isha | 1 | 78000 | 108333.0 | 6 |
| Diya | 1 | 60000 | 108333.0 | 6 |
| Dev | 3 | 110000 | 80667.0 | 3 |
| Anika | 3 | 68000 | 80667.0 | 3 |
| Yash | 3 | 64000 | 80667.0 | 3 |
+------------+---------------+--------+--------------+-----------+Engineering rows all show 108,333 and a size of 6; Marketing rows show 80,667 and a size of 3. Two aggregates share the same window here, and you can add as many as you like.
Compare that with the grouped version of the same idea:
SELECT d.name AS department, COUNT(*) AS headcount, ROUND(AVG(e.salary), 0) AS avg_salary
FROM employees e
JOIN departments d ON d.id = e.department_id
GROUP BY d.name
ORDER BY d.name;+-------------+-----------+------------+
| department | headcount | avg_salary |
+-------------+-----------+------------+
| Engineering | 6 | 108333.0 |
| Finance | 2 | 92500.0 |
| Marketing | 3 | 80667.0 |
| Sales | 5 | 86400.0 |
| Support | 3 | 65667.0 |
+-------------+-----------+------------+Same numbers, but one row per department instead of one row per employee. Use GROUP BY when you want the summary only, and a window function when you want the summary and the detail.
Using the window value in a calculation
Because the window value sits on the same row, you can do arithmetic with it. Here is how each Sales employee compares with the Sales average, and what share of the department payroll their salary is:
SELECT first_name, salary,
salary - ROUND(AVG(salary) OVER (PARTITION BY department_id), 0) AS vs_dept_average,
ROUND(100.0 * salary / SUM(salary) OVER (PARTITION BY department_id), 1) AS pct_of_dept_payroll
FROM employees
WHERE department_id = 2
ORDER BY salary DESC, first_name;+------------+--------+-----------------+---------------------+
| first_name | salary | vs_dept_average | pct_of_dept_payroll |
+------------+--------+-----------------+---------------------+
| Vikram | 150000 | 63600.0 | 34.7 |
| Neha | 90000 | 3600.0 | 20.8 |
| Sameer | 90000 | 3600.0 | 20.8 |
| Tara | 52000 | -34400.0 | 12.0 |
| Aman | 50000 | -36400.0 | 11.6 |
+------------+--------+-----------------+---------------------+Notice that WHERE department_id = 2 is applied first, so the window only sees the five Sales employees. Window functions work on the rows that survive WHERE.
Windows after GROUP BY
You can also apply a window function to grouped results. The window then sees one row per group:
SELECT d.name AS department, COUNT(*) AS headcount,
SUM(COUNT(*)) OVER () AS company_total
FROM employees e
JOIN departments d ON d.id = e.department_id
GROUP BY d.name
ORDER BY d.name;+-------------+-----------+---------------+
| department | headcount | company_total |
+-------------+-----------+---------------+
| Engineering | 6 | 19 |
| Finance | 2 | 19 |
| Marketing | 3 | 19 |
| Sales | 5 | 19 |
| Support | 3 | 19 |
+-------------+-----------+---------------+The total is 19, not 20, because the inner join drops Ravi (employee 18), who has no department. The order of work is: join, WHERE, GROUP BY, HAVING, then window functions, then ORDER BY and LIMIT.
When to choose a window function
A good rule of thumb: if the answer should have one row per group, use GROUP BY. If the answer should keep one row per original record and simply add context to it, use a window function. Typical examples are comparing each person with their team, showing each order beside the customer's total, or labelling every row with its share of the whole. You can also combine the two, as in the last example: group first, then run a window function over the grouped rows. Think of the window as a lens that moves over the rows, looking at a group of neighbours for every row in turn, while the rows themselves stay exactly as they were.
Differences between databases
Window functions are part of standard SQL and work in current versions of PostgreSQL and SQL Server, in MySQL 8.0 and newer, and in SQLite 3.25 and newer. MySQL 5.7 and earlier do not have them. The ROUND display (90050.0 or 90050) varies by database. For MySQL specifics, see the MySQL window functions lesson.
Common mistakes
- Using a window function in WHERE.
WHERE salary > AVG(salary) OVER ()is an error (SQLite reports "misuse of window function"). Calculate the window value in a CTE or subquery, then filter in the outer query. - Forgetting the OVER.
AVG(salary)withoutOVERis a normal aggregate and will either collapse rows or fail when mixed with plain columns. - Expecting a window to collapse rows. If you want one row per group, you want
GROUP BY, notPARTITION BY. - Leaving out PARTITION BY by accident.
OVER ()calculates across everything, which gives one repeated number instead of one per department.
Exercise
For the employees in department 3, show each first name, department id and salary, plus the highest salary in their department (dept_max) and how far below that maximum they are (gap_to_max).
Show one possible answer
SELECT first_name, department_id, salary,
MAX(salary) OVER (PARTITION BY department_id) AS dept_max,
MAX(salary) OVER (PARTITION BY department_id) - salary AS gap_to_max
FROM employees
WHERE department_id = 3
ORDER BY salary DESC;+------------+---------------+--------+----------+------------+
| first_name | department_id | salary | dept_max | gap_to_max |
+------------+---------------+--------+----------+------------+
| Dev | 3 | 110000 | 110000 | 0 |
| Anika | 3 | 68000 | 110000 | 42000 |
| Yash | 3 | 64000 | 110000 | 46000 |
+------------+---------------+--------+----------+------------+Key points
- A window function calculates a value across a set of related rows but keeps every row in the result, unlike GROUP BY, which collapses rows.
- OVER () with nothing inside means the window is the whole result; PARTITION BY splits the rows into separate windows, such as one per department.
- Any aggregate (SUM, AVG, COUNT, MIN, MAX) can become a window function by adding OVER.
- Window functions run after WHERE, GROUP BY and HAVING, so you cannot use them in WHERE; wrap the query in a CTE or subquery and filter outside.