Window Functions vs GROUP BY, Explained the Super Simple Way
What is a window function?
A window function is any function used with OVER (...).
SUM(amount) OVER (PARTITION BY name) - The whole
SUM(amount) OVER (...)is the window function -
PARTITION BYgoes inside it and says which groups to calculate over
PARTITION BY is optional. If you leave it out, the whole table is treated as one group.
Note: "Table partitioning" (
CREATE TABLE ... PARTITION BY), which physically splits a table, uses the same keyword but is unrelated to window functions.
GROUP BY vs PARTITION BY
The short answer: this is the only difference.
- GROUP BY: splits rows into groups and collapses each group into one row
- PARTITION BY: splits rows into groups but keeps every row (each row gets the calculated value)
Sample table
sales table
| name | amount |
|---|---|
| Alice | 100 |
| Alice | 200 |
| Bob | 500 |
With GROUP BY
SELECT name, SUM(amount) FROM sales GROUP BY name; | name | sum |
|---|---|
| Alice | 300 |
| Bob | 500 |
Each name is collapsed into one row. The original "100" and "200" rows are gone.
With PARTITION BY
SELECT name, amount, SUM(amount) OVER (PARTITION BY name) AS total FROM sales; | name | amount | total |
|---|---|---|
| Alice | 100 | 300 |
| Alice | 200 | 300 |
| Bob | 500 | 500 |
You still have 3 rows, and each row gets the total for its name.
Why is this useful?
Because the rows are kept, you can use the original value and the total in the same row.
For example, you can get "what share of this person's sales is this one sale" in a single query.
SELECT name, amount, amount / SUM(amount) OVER (PARTITION BY name) AS ratio FROM sales; | name | amount | ratio |
|---|---|---|
| Alice | 100 | 0.33 |
| Alice | 200 | 0.67 |
| Bob | 500 | 1.00 |
ROW_NUMBER: numbering rows within each group
ROW_NUMBER() is a window function that numbers rows 1, 2, 3… within each group.
Adding ORDER BY inside OVER (...) sets the order within each group.
SELECT name, amount, ROW_NUMBER() OVER (PARTITION BY name ORDER BY amount DESC) AS row_num FROM sales; | name | amount | row_num |
|---|---|---|
| Alice | 200 | 1 |
| Alice | 100 | 2 |
| Bob | 500 | 1 |
Each name's rows are numbered from the largest amount down.
Common pattern: get the top row of each group
Filter on row_num = 1 to get each person's highest sale.
SELECT * FROM ( SELECT name, amount, ROW_NUMBER() OVER (PARTITION BY name ORDER BY amount DESC) AS row_num FROM sales ) t WHERE row_num = 1; | name | amount | row_num |
|---|---|---|
| Alice | 200 | 1 |
| Bob | 500 | 1 |
row_num is created in the SELECT, so you can't use it in the WHERE of the same query. That's why it's wrapped in a subquery first.
ROW_NUMBER vs RANK
They differ in how they number ties.
| amount | ROW_NUMBER | RANK |
|---|---|---|
| 500 | 1 | 1 |
| 500 | 2 | 1 |
| 300 | 3 | 3 |
-
ROW_NUMBER: always gives a different number, even for ties (1, 2, 3) -
RANK: gives ties the same number, then skips ahead (1, 1, 3)