The function is an ordinary aggregate — SUM, AVG, COUNT, MIN, MAX all work — and the OVER (...) turns it into a window function. Now salary - dept_avg, salary / dept_total and "is this the department maximum?" are each one expression on one row.

OVER () — the whole result as one window

Empty parentheses make every row's window the entire result:

SELECT name, salary,
       SUM(salary) OVER () AS payroll,
       ROUND(100 * salary / SUM(salary) OVER (), 1) AS pct_of_payroll,
       COUNT(*) OVER () AS headcount
FROM employees
ORDER BY salary DESC, name;

Before window functions, the same report needed a cross join to a one-row subquery (CROSS JOIN (SELECT SUM(salary) AS payroll FROM employees) t). Both still work; the window version says what it means in place.

PARTITION BY — one window per group

PARTITION BY splits the rows into groups, and each row's window is its own group. It takes any expressions, like GROUP BY:

SUM(amount) OVER (PARTITION BY region)                     -- per region
COUNT(*)    OVER (PARTITION BY customer_id, YEAR(order_date))  -- per customer per year
MAX(score)  OVER (PARTITION BY team)                       -- the team's best

Partitions do not change the number of rows and do not interact: one function may partition by department and the next by city in the same SELECT, each over the same rows. A partition of one row is perfectly fine — an HR department with one person has an average equal to that person's salary.

The comparison interviewers want, in one sentence: GROUP BY collapses each group to one row; PARTITION BY defines groups for the window but keeps every row.

ORDER BY inside OVER — order within the window

Adding ORDER BY inside OVER orders the rows of each partition for the function's calculation. Ranking functions (next lesson) and LAG/LEAD require it. For an aggregate, it changes the meaning: the window becomes "from the start of the partition up to this row", which turns SUM into a running total:

SELECT day, amount,
       SUM(amount) OVER (ORDER BY day) AS running_total
FROM daily_sales
ORDER BY day;

Exactly which rows count as "up to this row" — and what happens when two rows tie on day — is the subject of the frames lesson. For now remember two facts:

1. AVG(x) OVER (PARTITION BY d) — the whole partition.
2. AVG(x) OVER (PARTITION BY d ORDER BY t) — a growing window, from the partition's first row to the current one.

Adding an ORDER BY to a window "just to be tidy" therefore changes the numbers. Leave it out when you want the whole partition.

The window's ORDER BY does not order the output

This is the most common mistake with window functions. The ORDER BY inside OVER decides how the function sees the rows; it says nothing about the order the query returns them in. MySQL often happens to return rows in window order, because it sorted them to compute the window — and then a second window with a different ORDER BY, or a join, or a different plan reorders them. Always end with the query's own ORDER BY, and make it complete with tie-breakers:

SELECT name, dept, salary,
       RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS dept_rank
FROM employees
ORDER BY dept, dept_rank, name;     -- the output order, stated

When window functions are evaluated

The logical order of a SELECT, with windows added:

1. FROM and joins
2. WHERE
3. GROUP BY and aggregates
4. HAVING
5. window functions
6. SELECT list aliases, DISTINCT
7. ORDER BY
8. LIMIT

Three consequences follow.

WHERE cannot use a window function — the windows have not been computed yet. Filtering on a rank or a running total needs a CTE or derived table:

WITH ranked AS (
  SELECT name, dept, salary,
         RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS dept_rank
  FROM employees
)
SELECT name, dept, salary FROM ranked WHERE dept_rank = 1 ORDER BY dept;

Windows see only the rows that survived WHERE. WHERE dept <> 'HR' removes HR before any window is computed, so a COUNT(*) OVER () counts the rest.

Windows can run over grouped rows. After GROUP BY, each group is a row, and a window function can work across groups — including over an aggregate:

SELECT dept,
       SUM(salary) AS dept_payroll,
       ROUND(100 * SUM(salary) / SUM(SUM(salary)) OVER (), 1) AS pct
FROM employees
GROUP BY dept
ORDER BY dept_payroll DESC, dept;

SUM(SUM(salary)) OVER () reads oddly but is exact: the inner SUM is the group's aggregate, the outer SUM is a window over the grouped rows.

Named windows

When several functions share one window, name it once with a WINDOW clause (after HAVING, before ORDER BY):

SELECT name, dept, salary,
       AVG(salary) OVER w AS dept_avg,
       MAX(salary) OVER w AS dept_max,
       COUNT(*)    OVER w AS dept_size
FROM employees
WINDOW w AS (PARTITION BY dept)
ORDER BY dept, name;

A named window can also be extended: OVER (w ORDER BY salary) adds an ORDER BY to w's partitioning. MySQL, PostgreSQL and SQLite support WINDOW; SQL Server added it only in 2022.

Rounding window averages

An average over integers is rarely an integer, and different engines print different numbers of decimals. Wrap window averages in ROUND(…, 2) (or the precision the report needs) whenever the value is shown — the exercises always do.

In other databases

PostgreSQL, SQL Server, Oracle and SQLite all support window functions with the same OVER (PARTITION BY … ORDER BY …) syntax; PostgreSQL and Oracle had them a decade before MySQL. Code written for MySQL 5.7 emulates them with correlated subqueries ((SELECT AVG(salary) FROM employees e2 WHERE e2.dept = e.dept)) or with user variables; the window version is clearer and usually much faster.

Pitfalls

• Expecting the window's ORDER BY to order the output.
• Adding ORDER BY to an aggregate's window and getting running values instead of partition totals.
• Filtering on a window function in WHERE or HAVING.
• Forgetting that WHERE has already removed rows before the window sees them.
• Printing unrounded window averages.

Key takeaways

• function OVER (...) computes over a window of related rows and keeps every row.
• PARTITION BY sets the groups; OVER () is one window over the whole result.
• ORDER BY inside OVER orders rows for the function; for aggregates it makes a growing window.
• Windows run after WHERE, GROUP BY and HAVING — filter on them in an outer query.
• The query's own ORDER BY decides the output order; always write it.