Window Functions — The OVER Clause

GROUP BY collapses many rows into one. A window function computes across many rows and keeps every row. That one difference solves a whole class of problem that otherwise needs a self join or a correlated subquery.

SQL▶ Run
SELECT first_name,
       salary,
       ROUND(AVG(salary) OVER (), 2) AS company_average
FROM employees
WHERE salary IS NOT NULL
ORDER BY salary DESC;

Nine rows in, nine rows out — each carrying the company average alongside its own salary. With GROUP BY you would get one row and lose the names.

OVER () is what makes it a window function. Empty brackets mean the window is every row in the result.

Comparing a row to its group

SQL▶ Run
SELECT first_name,
       dept_id,
       salary,
       ROUND(AVG(salary) OVER (PARTITION BY dept_id), 2) AS dept_average,
       ROUND(salary - AVG(salary) OVER (PARTITION BY dept_id), 2) AS vs_dept
FROM employees
WHERE salary IS NOT NULL
ORDER BY dept_id, salary DESC;

PARTITION BY dept_id splits the rows into groups and computes the average within each — but still returns every row. Compare with the correlated subquery in lesson 25 that answered a similar question: this reads better, and the database makes one pass instead of one per row.

PARTITION BY is to windows what GROUP BY is to aggregates — with the rows kept.

Anatomy of OVER

function() OVER (
    PARTITION BY <split the rows into groups>
    ORDER BY     <order within each group>
    <frame>      <which rows within the group>
)

All three parts are optional:

• No PARTITION BY — one window over everything.
• No ORDER BY — the whole partition is the window; order is irrelevant.
• No frame — a default applies, which the running-totals lesson covers in detail.

Where window functions may appear

Only in SELECT and ORDER BY. Never in WHERE, GROUP BY or HAVING.

The execution order from lesson 10 explains why: window functions are evaluated after WHERE, GROUP BY and HAVING, at roughly the same stage as SELECT. A WHERE clause cannot filter on something that has not been computed yet.

So this is an error:

SELECT first_name, salary
FROM employees
WHERE ROW_NUMBER() OVER (ORDER BY salary DESC) <= 3;

And this is how you write it — compute the window in a CTE, filter outside:

SQL▶ Run
WITH ranked AS (
  SELECT first_name, salary,
         ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
  FROM employees
  WHERE salary IS NOT NULL
)
SELECT first_name, salary
FROM ranked
WHERE rn <= 3
ORDER BY rn;

That wrapping pattern — window in a CTE, filter in the outer query — is the single most useful thing in this module. Lesson 32 uses it repeatedly.

Aggregates you already know, as windows

Every aggregate from lesson 15 works with OVER:

SQL▶ Run
SELECT dept_id,
       first_name,
       salary,
       COUNT(*)    OVER (PARTITION BY dept_id) AS dept_size,
       MAX(salary) OVER (PARTITION BY dept_id) AS dept_top,
       MIN(salary) OVER (PARTITION BY dept_id) AS dept_bottom
FROM employees
WHERE salary IS NOT NULL
ORDER BY dept_id, salary DESC;

Mixing windows with GROUP BY

They operate at different stages, so they combine — the window sees the grouped rows. This is the clean fix for lesson 18's percentage-of-total, which needed a scalar subquery:

SQL▶ Run
SELECT status,
       COUNT(*) AS orders,
       ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct_of_all
FROM orders
GROUP BY status
ORDER BY orders DESC, status;

SUM(COUNT(*)) OVER () looks strange and is exactly right: COUNT(*) aggregates within each status, then SUM(...) OVER () totals those counts across all the grouped rows. One pass, no subquery.

Support

Window functions are standard and everywhere current: PostgreSQL 8.4+, SQL Server 2005+, Oracle, SQLite 3.25+, and MySQL 8.0+. MySQL 5.7 has none of this, which is why so much older code uses self joins and user variables instead.

Common mistakes

• A window function in WHERE or HAVING — wrap it in a CTE and filter outside.
• Expecting PARTITION BY to reduce the row count — it never does; that is GROUP BY.
• Forgetting ORDER BY inside OVER — for ranking and running totals it changes the answer entirely.
• Assuming MySQL 5.7 supports it — it does not.

Interview question

What is the difference between GROUP BY and PARTITION BY?

Both divide rows into groups. GROUP BY collapses each group to one row; PARTITION BY computes across the group while keeping every row. The follow-up worth having ready: you cannot filter on a window function in WHERE, because windows are evaluated after it — you wrap the query.

Check yourself

1. Show every employee with their department's headcount beside them.
2. Why is WHERE ROW_NUMBER() OVER (…) <= 3 an error?
3. What does OVER () with empty brackets mean?