SQL / WINDOW FUNCTIONS
PARTITION BY versus GROUP BY
Decide between GROUP BY and PARTITION BY by whether the result needs one row per group or a group total attached to every detail row, and combine both.
What you will learn
- Predict row counts: GROUP BY gives one row per group, PARTITION BY keeps every row.
- Rewrite a correlated aggregate subquery as one SUM(...) OVER (PARTITION BY key).
- Put two overlapping partitionings in one SELECT, which GROUP BY cannot express.
- Stack a window function on top of GROUP BY output; windows run after grouping.
Understanding PARTITION BY versus GROUP BY
GROUP BY is a reduction. Given five salary rows, GROUP BY dept hands the engine two bags of rows and asks for one output row per bag, so after grouping there is no row that belongs to Ada any more, only the Eng bag and the Sales bag. That is why the select list of a grouped query may mention grouping keys and aggregates and nothing else: the individual salaries are no longer addressable.
PARTITION BY reduces nothing; it only tells an aggregate which rows to look at. For each row the engine forms that row's partition, meaning every row sharing the partition key, computes SUM(salary) over it, and writes the answer beside the row's own columns. So SUM(salary) OVER (PARTITION BY dept) is the same 360 that GROUP BY produces for Eng, copied onto all three Eng rows, with name and salary still intact.
The two clauses live at different stages of the query, which is why they can appear together. Grouping happens before window functions, so in a grouped query the window sees one row per group: PARTITION BY then partitions groups rather than base rows, and SUM(SUM(salary)) OVER () adds department totals into a company total. This also explains the asymmetry in flexibility, since a query commits to a single GROUP BY granularity but can carry as many different OVER (PARTITION BY ...) clauses as you need.
-- Same five rows, two different shapes of answer.
WITH salaries(dept, name, salary) AS (
SELECT 'Eng', 'Ada', 120 UNION ALL
SELECT 'Eng', 'Grace', 140 UNION ALL
SELECT 'Eng', 'Linus', 100 UNION ALL
SELECT 'Sales', 'Mia', 90 UNION ALL
SELECT 'Sales', 'Omar', 70
)
SELECT dept, SUM(salary) AS dept_total
FROM salaries
GROUP BY dept
ORDER BY dept;
WITH salaries(dept, name, salary) AS (
SELECT 'Eng', 'Ada', 120 UNION ALL
SELECT 'Eng', 'Grace', 140 UNION ALL
SELECT 'Eng', 'Linus', 100 UNION ALL
SELECT 'Sales', 'Mia', 90 UNION ALL
SELECT 'Sales', 'Omar', 70
)
SELECT dept, name, salary,
SUM(salary) OVER (PARTITION BY dept) AS dept_total
FROM salaries
ORDER BY dept, name;PARTITION BY chooses which rows an aggregate looks at, while GROUP BY chooses how many rows come out.
Worked examples
A window function on top of GROUP BY
Shows that window functions run after grouping, so PARTITION BY and OVER () see grouped rows, not base rows.
WITH salaries(dept, name, salary) AS (
SELECT 'Eng', 'Ada', 120 UNION ALL
SELECT 'Eng', 'Grace', 140 UNION ALL
SELECT 'Eng', 'Linus', 100 UNION ALL
SELECT 'Sales', 'Mia', 90 UNION ALL
SELECT 'Sales', 'Omar', 70
)
SELECT dept,
SUM(salary) AS dept_total,
SUM(SUM(salary)) OVER () AS company_total,
COUNT(*) AS headcount
FROM salaries
GROUP BY dept
ORDER BY dept;Example explained
Line 1GROUP BY dept collapses five rows to two, so the window step receives two rows as its input.
Line 2The inner SUM(salary) is the group aggregate; the outer SUM(...) OVER () adds those two group totals, giving 520 rather than any per-row figure.
Line 3OVER () with no PARTITION BY makes a single partition holding every grouped row, so both rows show the same 520.
Line 4COUNT(*) still reports base rows per group (3 and 2) because it is evaluated during grouping, before any window function runs.
Two partitionings in one row
Demonstrates overlapping groupings that a single GROUP BY clause cannot produce.
WITH staff(dept, name, grade, salary) AS (
SELECT 'Eng', 'Ada', 'senior', 120 UNION ALL
SELECT 'Eng', 'Grace', 'senior', 140 UNION ALL
SELECT 'Eng', 'Linus', 'junior', 100 UNION ALL
SELECT 'Sales', 'Mia', 'senior', 90 UNION ALL
SELECT 'Sales', 'Omar', 'junior', 70
)
SELECT dept, name, grade, salary,
SUM(salary) OVER (PARTITION BY dept) AS dept_total,
SUM(salary) OVER (PARTITION BY grade) AS grade_total
FROM staff
ORDER BY dept, name;Example explained
Line 1Each OVER clause defines its own partitioning, so Ada's row belongs to the Eng partition and the senior partition at the same time.
Line 2Ada's grade_total of 350 is 120 + 140 + 90, every senior row regardless of department.
Line 3GROUP BY dept, grade cannot produce this: it would return four dept-and-grade subtotals instead of two independent totals per employee.
Line 4No column has to be repeated in a grouping clause, because nothing was collapsed and the select list stays free.
What PARTITION BY replaces
The correlated subquery form that produces the same result, which shows what a partition actually is.
WITH salaries(dept, name, salary) AS (
SELECT 'Eng', 'Ada', 120 UNION ALL
SELECT 'Eng', 'Grace', 140 UNION ALL
SELECT 'Eng', 'Linus', 100 UNION ALL
SELECT 'Sales', 'Mia', 90 UNION ALL
SELECT 'Sales', 'Omar', 70
)
SELECT s.dept, s.name, s.salary,
(SELECT SUM(t.salary) FROM salaries t WHERE t.dept = s.dept) AS dept_total
FROM salaries s
ORDER BY s.dept, s.name;Example explained
Line 1The subquery is a GROUP BY in disguise: it aggregates the rows whose dept matches the current row, which is exactly the row set PARTITION BY dept defines.
Line 2It names the table twice and re-runs per row, while SUM(salary) OVER (PARTITION BY dept) reaches the same numbers from one pass over rows sorted by dept.
Line 3Each extra per-department figure needs another subquery here, whereas the window form only needs another OVER clause.
Line 4The forms differ on NULL: t.dept = s.dept never matches a NULL dept so those rows get NULL, but PARTITION BY collects all NULL depts into one partition and gives them a total.
Important notes
SELECT DISTINCT dept, SUM(salary) OVER (PARTITION BY dept) can imitate GROUP BY, but it aggregates every row and then removes duplicates, so use GROUP BY when one row per group is all you want.
If your engine refuses an aggregate inside a window argument such as SUM(SUM(salary)) OVER (), put the GROUP BY in a derived table and apply the window function in the outer query; the result is identical because windows run after grouping either way.
Common mistakes
Keeping GROUP BY dept and adding SUM(SUM(salary)) OVER (PARTITION BY dept), then concluding the window function is broken: each partition now contains one grouped row, so the window total simply repeats the group total.
Selecting name next to SUM(salary) with GROUP BY dept. PostgreSQL and SQL Server reject the query, while SQLite and MySQL without ONLY_FULL_GROUP_BY return an arbitrary name beside the department total, which reads like a report and is silently wrong.
Silencing that error with GROUP BY dept, name: every group becomes a single employee, so SUM(salary) returns that person's own salary and the department total vanishes from the result.
Try it yourself
Change, predict, then run
Build the five-row salaries CTE in a browser SQL editor and write one query returning every employee with their department total and the company total. Then write the GROUP BY dept version and confirm it returns 2 rows against the first query's 5, with the department totals matching.
Open the SQL workspaceCheck your understanding
A 200-row table gives 4 rows from SELECT dept, SUM(salary) AS total FROM emp GROUP BY dept. You rewrite it as SELECT dept, SUM(salary) OVER (PARTITION BY dept) AS total FROM emp. What comes back?
- 200 rows, each carrying the total of its own department
- 4 rows, identical to the grouped query
- 200 rows, each carrying the total of all 200 salaries
- An error, because dept is not listed in a GROUP BY clause
Show answer
With no GROUP BY there is nothing to collapse, so all 200 rows survive and each one shows the sum over the partition it belongs to. Option 1 is the tempting reading of PARTITION BY as a synonym for GROUP BY, but a partition only limits which rows the aggregate reads and never removes rows; you would need DISTINCT or GROUP BY to get 4. Option 3 fails because the grouped-column rule only applies to queries that actually group.