Skip to content
Tutorials Center
My learning

Topics

Lesson 49 of 125 ยท Window Functions

Window Functions

Learn SQL window functions with OVER, PARTITION BY and ORDER BY. Add totals, averages and shares to every row without collapsing rows like GROUP BY.

GROUP BY is great at answering "what is the average salary per department?", but it pays a price: the individual rows disappear. You get one row per department, and the employees are gone. Very often you want both: every employee and their department's average next to them, so you can compare.

Window functions do exactly that. A window function calculates a value over a set of rows related to the current row (its window) and returns that value on every row, without collapsing anything. They power rankings, running totals, month-over-month changes, percentages of a total and much more, and they are one of the most valuable SQL skills for analysis work.

This lesson introduces the core syntax: OVER, PARTITION BY and ORDER BY inside a window. The rest of this chapter builds on it with rankings, running totals, comparing rows and frames.

GROUP BY collapses, windows don't

Start with an ordinary aggregate. This query returns one row per department:

SQL GROUP BY: one row per group ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Now the window version. Adding OVER (...) after AVG(salary) turns the aggregate into a window function. Every employee stays, and each row gets the average of its own department:

SQL Window: every row keeps its details ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

The result has 34 rows, one per employee with a department. All Engineering rows (department 1) show 87500, all Design rows show 83000, and so on. You can now see at a glance who is above or below their department's average, something GROUP BY alone can't show.

GROUP BY Window function
Rows in the result One per group One per input row
Can show row details next to the aggregate? No (only grouped columns) Yes
Keyword GROUP BY clause OVER (...) after the function
Typical question "Total per category?" "Each item's share of its category?"

The anatomy of OVER

The OVER clause defines the window. It has three optional parts, always in this order:

SQL
function_name(arguments) OVER (
  PARTITION BY ...   -- split rows into groups (optional)
  ORDER BY ...       -- order rows inside each group (optional)
  ROWS/RANGE ...     -- frame: which rows around the current one (optional)
)

Leave everything out and the window is the whole result. SUM(salary) OVER () is the total payroll of the company, repeated on every row. That makes "percentage of total" a one-liner:

SQL Share of total payroll ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

The company spends 3,253,000 on salaries in total, and the CEO's 210,000 is 6.5% of it. Note 100.0 rather than 100: in SQLite, integer divided by integer is integer division, and you'd get zeros. (See Calculated Columns for why.)

PARTITION BY: a window per group

PARTITION BY splits the rows into independent groups, like GROUP BY does, but without merging them. The window function restarts in every partition. You can partition by one column, several columns or an expression.

This query compares each employee with their department in three ways at once:

SQL Comparing each row with its partition ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

In Design, Farah earns 55,000 more than the department average of 83,000, while Wen is 27,000 below it. Every aggregate you know (COUNT, SUM, AVG, MIN, MAX, total, group_concat) works as a window function this way.

The WHERE runs first. Windows only see rows that survive WHERE. Because this query filters to Design and Sales before the windows run, dept_size and dept_max still describe whole departments: the filter removed whole partitions, not rows inside them. If you filtered on salary instead, the averages would change.

ORDER BY inside OVER

An ORDER BY inside OVER sorts the rows within each partition for the window function. For ranking functions, that order decides who is first. For aggregates, it changes the meaning: the window becomes "all rows from the start of the partition up to the current row", which produces a running total:

SQL ORDER BY turns SUM into a running total ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Read the last two columns as "the payroll and headcount if the company had stopped hiring on this date". Kofi joined first (210,000, headcount 1), Omar next (356,000, headcount 2), and so on. The running totals lesson covers this pattern in depth, and Window Frames in Depth explains exactly which rows "up to the current row" includes when values tie.

The ORDER BY inside OVER is independent of the query's final ORDER BY. The window can be computed in hire-date order while the result is displayed sorted by name. When you care about the output order, always add an outer ORDER BY.

The window function families

Family Functions Lesson
Aggregates SUM, AVG, COUNT, MIN, MAX, total, group_concat this lesson, Running Totals
Ranking ROW_NUMBER, RANK, DENSE_RANK, NTILE Ranking
Offset (value) LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE LAG and LEAD
Distribution PERCENT_RANK, CUME_DIST Percentiles

Ranking, offset and distribution functions exist only as window functions, so they always need OVER. Writing ROW_NUMBER() without it is an error.

Where window functions are allowed

Window functions are calculated late in the logical order of a query, after FROM, WHERE, GROUP BY and HAVING, just before the final ORDER BY and LIMIT:

  1. FROM and joins
  2. WHERE
  3. GROUP BY
  4. HAVING
  5. Window functions
  6. SELECT list output (DISTINCT)
  7. ORDER BY
  8. LIMIT / OFFSET

That's why you can use them in the SELECT list and in ORDER BY, but not in WHERE, GROUP BY or HAVING. This query fails on purpose:

SQL Window functions can't go in WHERE ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

SQLite answers Error: misuse of window function AVG(). The fix is to compute the window in a CTE or subquery and filter the result one level up:

SQL Filter on a window result with a CTE ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Twelve employees earn more than the company average of about 90,361. You'll use this "window in a CTE, filter outside" pattern constantly, for example to keep the top 3 rows per group.

Windows over grouped results

Because windows run after GROUP BY, you can apply a window to aggregated values. The pattern looks odd at first, an aggregate inside a window function, but it reads naturally: SUM(SUM(x)) OVER () means "the total of the group totals".

This report shows each department's payroll and its share of the company's payroll, in one query:

SQL A window over GROUP BY results ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

The inner SUM(e.salary) is the normal group aggregate (one value per department). The outer SUM(...) OVER () then adds those six values together across the grouped result. Engineering's 875,000 is 29.2% of the 2,995,000 paid to people who belong to a department (the CEO and the contractor have no department and are excluded by the join).

Common mistakes

Most window-function bugs come from a handful of misunderstandings:

  • Forgetting that WHERE runs first. If you filter to one month and then compute SUM(x) OVER (), the "total" is that month's total, not the year's. When you need a total over more rows than you display, compute the window in a CTE over all rows, then filter outside.
  • Expecting GROUP BY behavior. Window functions return one row per input row. If you only want one row per group, use GROUP BY (or DISTINCT, if every column you select is the same within the group).
  • Leaving out a tie-breaker. Any window with ORDER BY can meet equal values. Running totals then jump by several rows at once, and ROW_NUMBER becomes unpredictable. Add a unique column, such as id, as the last sort key.
  • Forgetting about NULL. Aggregate windows skip NULLs just like normal aggregates: AVG(bonus) OVER () averages only the rows that have a bonus. Use COALESCE(bonus, 0) if missing should count as zero.
  • Mixing up the two ORDER BYs. The one inside OVER controls the calculation; the one at the end of the query controls the display. They can differ, and you usually want both.

Window functions are also not free. Each distinct window definition (each different PARTITION BY/ORDER BY combination) may need its own sort of the data. Several functions sharing the same window are cheap; many different windows over a large table can be slow. The WINDOW clause in Window Frames in Depth helps you share definitions.

Support across databases

Window functions are part of the SQL standard (SQL:2003, extended in SQL:2011) and every major database supports the core syntax:

Database Window functions since Notes
SQLite 3.25 (2018) Full frames, GROUPS, EXCLUDE and FILTER since 3.28/3.30
PostgreSQL 8.4 Very complete
MySQL 8.0 Not in 5.7 or older
MariaDB 10.2
SQL Server 2005 (ranking), 2012 (frames, LAG/LEAD)
Oracle 8i Calls them "analytic functions"

If you're stuck on MySQL 5.7, window results have to be emulated with correlated subqueries or self joins, which are slower and harder to read. That's a strong reason to upgrade.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. A table has 50 rows in 5 departments. How many rows does SELECT name, AVG(salary) OVER (PARTITION BY dept) FROM staff; return?

    Window functions never collapse rows. Each of the 50 rows is returned with its department's average next to it.
  2. 2. What does SUM(amount) OVER () compute?

    An empty OVER () makes the whole result one window, so every row gets the grand total.
  3. 3. Why does WHERE salary > AVG(salary) OVER () fail?

    Windows run after FROM, WHERE, GROUP BY and HAVING. Compute the window in a CTE or subquery and filter in the outer query.
  4. 4. What changes when you add ORDER BY inside the OVER clause of SUM()?

    With ORDER BY in the window, aggregates use a frame that ends at the current row, so SUM becomes cumulative.
  5. 5. What does SUM(SUM(salary)) OVER () mean in a query with GROUP BY department?

    The inner SUM is the group aggregate; the outer SUM ... OVER () adds those group results together, because windows run after GROUP BY.

Summary

  • A window function adds OVER (...) to a function and returns a value on every row, without collapsing rows.
  • OVER () uses the whole result as the window; perfect for grand totals and percentages.
  • PARTITION BY restarts the calculation for each group.
  • ORDER BY inside OVER orders rows for the function and turns aggregates into running calculations.
  • Windows run after WHERE, GROUP BY and HAVING; to filter on a window result, wrap it in a CTE or subquery.
  • SUM(SUM(x)) OVER () applies a window to grouped results.

Next: ROW_NUMBER, RANK and DENSE_RANK, where you'll number rows, handle ties and find the top N per group.