What is the difference between PARTITION BY in a window function and GROUP BY in an aggregate query?
GROUP BY collapses multiple rows into one row per group and discards individual row data. PARTITION BY divides rows into groups for the window function calculation but preserves every row in the output — each row retains its own columns plus the computed window value.
How to think about it
This is the central conceptual split in window functions. Many analysts can write OVER (PARTITION BY ...) but haven’t internalised why it keeps every row — and that understanding is what drives correct query design. The one-sentence version: GROUP BY collapses rows; PARTITION BY does not.
GROUP BY returns one row per group and throws away the individual rows. PARTITION BY computes the same aggregate per group but leaves every original row in place, attaching the group statistic alongside. That’s what lets you compare each row to its group in a single query.
A worked example — every row, plus its group’s average
Compute each employee’s department average and the gap to it, without losing a single employee:
SELECT name, dept, salary,
ROUND(AVG(salary) OVER (PARTITION BY dept), 0) AS dept_avg,
salary - ROUND(AVG(salary) OVER (PARTITION BY dept), 0) AS vs_dept_avg
FROM employees
ORDER BY dept, salary DESC;| name | dept | salary | dept_avg | vs_dept_avg |
|---|---|---|---|---|
| Aarav | Eng | 120000 | 108333.0 | 11667.0 |
| Chen | Eng | 110000 | 108333.0 | 1667.0 |
| Bea | Eng | 95000 | 108333.0 | -13333.0 |
| Farah | Sales | 85000 | 79000.0 | 6000.0 |
| Dara | Sales | 80000 | 79000.0 | 1000.0 |
| Eli | Sales | 72000 | 79000.0 | -7000.0 |
All six employees survive, and dept_avg repeats the same value down each department (108333 for Eng, 79000 for Sales) because the window recomputes it per partition. The vs_dept_avg column — only possible because the rows weren’t collapsed — shows Aarav 11,667 above his Eng peers and Bea 13,333 below. A GROUP BY dept here would return just two rows and make that per-employee comparison impossible without a self-join. (ROUND(..., 0) returns a float in SQLite, hence the .0.)
When to use which
| Need | Use |
|---|---|
| One summary row per group | GROUP BY |
| Row-level data alongside a group statistic | window function with PARTITION BY |
| Group-level summaries that themselves need ranking | GROUP BY + a window function over the aggregates |
That last row is a real combo: RANK() OVER (ORDER BY AVG(salary) DESC) on top of a GROUP BY department ranks the departments by average pay — GROUP BY collapses first, then the window ranks the collapsed rows.