OVER and PARTITION BY: windows vs GROUP BY

Part of the free LogicWiz SQL & Databases course, Module 11: Window functions. The queries and practice run in the app on real Postgres.

Aarav wants every innings of the season next to that match's total runs, to see who carried the team. GROUP BY match_id gives the totals, but the 39 innings shrink to 10 rows. How do you keep every row and see the total?

You'll learn to

• Explain how a window function differs from GROUP BY
• Use OVER () for a whole-table value on every row
• Split rows into groups with PARTITION BY
• Filter on a window result using a subquery

Learn

GROUP BY squashes, a window looks around

GROUP BY collapses rows: 39 innings become 10 match rows, and the single innings are gone.

A window function keeps every row. For each row it looks at a group of related rows (its window), computes something like a SUM or a rank, and writes the answer on that row.

You turn an aggregate into a window function by adding OVER (...) after it. SUM(runs) alone collapses; SUM(runs) OVER (...) does not.

OVER (): the whole result is one window

SELECT match_id, player_id, runs,
       SUM(runs) OVER () AS all_runs,
       COUNT(*) OVER () AS innings
FROM batting
WHERE match_id = 1;

Empty brackets mean "all the rows the query has". Match 1 had 5 innings totalling 208 runs, and every row shows both numbers. Five rows went in, five rows came out.

PARTITION BY: one window per match

SELECT b.match_id, p.name, b.runs,
       SUM(b.runs) OVER (PARTITION BY b.match_id) AS match_runs,
       ROUND(100.0 * b.runs / SUM(b.runs) OVER (PARTITION BY b.match_id), 1) AS pct
FROM batting b
JOIN players p ON p.player_id = b.player_id
WHERE b.match_id IN (1, 2)
ORDER BY b.match_id, b.runs DESC, p.name;

PARTITION BY match_id is like GROUP BY for the window only: each row sees just the rows of its own match. Kohli's 81 is 38.9% of the 208 runs in match 1.

GROUP BY or a window?

When a window runs

A query runs in this order: FROM and joins, WHERE, GROUP BY, HAVING, then the window functions, then SELECT names, DISTINCT, ORDER BY, LIMIT.

Two results follow:

• A window sees only the rows that survived WHERE. Filter to one player and SUM OVER () is that player's total, not the season's.
• WHERE runs before windows, so you cannot write a window function in WHERE. Compute it in a subquery, then filter outside.

This fails: a window in WHERE

SELECT match_id, player_id, runs
FROM batting
WHERE runs > AVG(runs) OVER (PARTITION BY match_id);

Postgres says window functions are not allowed in WHERE. The fix is on the next card.

The fix: compute in a subquery, filter outside

SELECT match_id, name, runs, match_avg
FROM (
  SELECT b.match_id, p.name, b.runs,
         ROUND(AVG(b.runs) OVER (PARTITION BY b.match_id), 1) AS match_avg
  FROM batting b
  JOIN players p ON p.player_id = b.player_id
) t
WHERE runs > match_avg
ORDER BY match_id, runs DESC;

The inner query adds match_avg to every innings. The outer query can now filter on it like any column. In the next module you will write the same thing with WITH.

Interviews open window rounds with: "What is the difference between GROUP BY and a window function?" Answer in one line: GROUP BY returns one row per group, a window function returns every row with the group value added. Then: "Can you use ROW_NUMBER in WHERE?" No, windows run after WHERE; wrap the query and filter outside.

MySQL supports window functions from version 8.0. Older MySQL (5.7) has none, which is why old answers online use self joins and variables. Snowflake and BigQuery add QUALIFY to filter on a window directly; Postgres and MySQL do not have it.

In one line

f(...) OVER (PARTITION BY x) adds a group value to every row without collapsing rows; it runs after WHERE, so filter on it from an outer query.

Interview corner

What is the difference between GROUP BY and a window function?

GROUP BY collapses each group into one row, so you can only show grouped columns and aggregates. A window function keeps every row and adds a value computed over related rows, e.g. SUM(runs) OVER (PARTITION BY match_id) shows each innings with its match total.

Can you use a window function in WHERE or HAVING?

No. Windows are computed after WHERE, GROUP BY and HAVING. Compute the window in a subquery or CTE and filter in the outer query: SELECT * FROM (SELECT ..., RANK() OVER (...) AS r FROM t) x WHERE r = 1. Some warehouses (Snowflake, BigQuery) offer QUALIFY for this.

What does OVER () with empty brackets mean?

The window is every row of the result (after WHERE). COUNT(*) OVER () puts the total row count on each row, handy for percent-of-total: 100.0 * amount / SUM(amount) OVER ().