GROUP BY and a window function's OVER() both compute an aggregate like AVG() across a set of related rows — that similarity is exactly what makes it easy to reach for the wrong one. GROUP BY collapses every row in a group down into a single summary row. A window function computes the same aggregate but leaves every original row exactly where it was, just with the aggregate attached as an extra column. Confusing the two doesn't produce a syntax error most of the time — it produces a result that's the wrong shape, and on SQLite specifically, sometimes a result that looks like the right shape while being quietly wrong underneath.
Verified by actually running this against SQLite — not hand-computed. Five employees across two departments.
CREATE TABLE employees(
employee_id INTEGER,
employee_name TEXT,
department TEXT,
salary REAL
);
INSERT INTO employees VALUES
(1,'Aarav','Engineering',90000),
(2,'Priya','Engineering',85000),
(3,'Karthik','Engineering',78000),
(4,'Meera','Sales',60000),
(5,'Rohan','Sales',55000);| employee_id | employee_name | department | salary |
|---|---|---|---|
| 1 | Aarav | Engineering | 90000 |
| 2 | Priya | Engineering | 85000 |
| 3 | Karthik | Engineering | 78000 |
| 4 | Meera | Sales | 60000 |
| 5 | Rohan | Sales | 55000 |
The goal: show each employee's salary next to their department's average, so it's obvious who's earning above or below the department norm — without losing anyone.
SELECT department, ROUND(AVG(salary), 2) AS avg_salary
FROM employees
GROUP BY department;| department | avg_salary |
|---|---|
| Engineering | 84333.33 |
| Sales | 57500 |
Five employees became two rows. The department averages are correct, but every individual employee — the exact thing "who's above or below average" needs — is gone. GROUP BY did precisely what it's designed to do: it reduced the table to one row per group. That's the problem, not a malfunction.
Trying to patch this by simply adding the aggregate straight into a normal row-level query looks tempting:
SELECT employee_name, department, salary, AVG(salary) AS avg_salary
FROM employees;| employee_name | department | salary | avg_salary |
|---|---|---|---|
| Aarav | Engineering | 90000 | 73600 |
Five rows became one. SQLite doesn't raise an error for mixing a plain column with an aggregate and no GROUP BY — it silently collapses the whole result to a single row, arbitrarily picks one row's values for the non-aggregated columns (here, whichever row it visited first), and pairs them with the true aggregate computed over all five employees, not just Aarav's department. The output looks exactly like a normal row of data. Nothing about it signals that four employees vanished and the average shown has nothing to do with the row it's sitting next to. Other engines like PostgreSQL refuse to run this at all — SQLite's willingness to run it anyway makes this the most dangerous version of the mistake, because it doesn't even fail loudly.
GROUP BY works in two phases: first it partitions rows into groups, then it reduces each group to exactly one output row. There is no path back to the original per-row detail once that reduction happens — it isn't hidden somewhere, it's genuinely gone from the result set. A window function skips the reduction step entirely: OVER (PARTITION BY department) tells the aggregate which rows to consider when computing its value, but every row from the original table still gets its own line in the output, with the computed aggregate just tacked on as one more column per row.
SELECT employee_name, department, salary,
ROUND(AVG(salary) OVER (PARTITION BY department), 2) AS dept_avg_salary,
ROUND(salary - AVG(salary) OVER (PARTITION BY department), 2) AS diff_from_avg
FROM employees
ORDER BY department, salary DESC;| employee_name | department | salary | dept_avg_salary | diff_from_avg |
|---|---|---|---|---|
| Aarav | Engineering | 90000 | 84333.33 | 5666.67 |
| Priya | Engineering | 85000 | 84333.33 | 666.67 |
| Karthik | Engineering | 78000 | 84333.33 | -6333.33 |
| Meera | Sales | 60000 | 57500 | 2500 |
| Rohan | Sales | 55000 | 57500 | -2500 |
All five employees are still here. PARTITION BY department tells AVG(salary) to compute separately for Engineering's three rows and Sales's two rows, but the partitioning only controls which rows get averaged together — it never removes a row from the output the way GROUP BY does. diff_from_avg — plain subtraction against the window function's result — is what actually answers "who's above or below average," and it only exists because every employee's own row survived long enough to be subtracted from.
None of this makes GROUP BY the wrong tool in general — it's the right one whenever the actual deliverable is the summary itself, not the detail underneath it. "What's the average salary per department" for a budgeting dashboard is a GROUP BY question — nobody asked for individual employees, so collapsing to one row per department is exactly correct, not a loss. The test is simple: if the final answer needs to name a specific row from the original table alongside its group's aggregate, that's a window function question. If the final answer is only the aggregate, GROUP BY is doing exactly its job.
| Need | Use | Row count in output |
|---|---|---|
| Only the summary (one row per group) | GROUP BY | Reduced to number of groups |
| Every row, plus its group's aggregate | Window function with OVER (PARTITION BY ...) | Unchanged — same as input |
| A per-row column mixed with a raw aggregate, no GROUP BY | Neither — SQLite silently collapses to one arbitrary row | 1, and it's meaningless |
The tell isn't the SQL syntax, which looks almost identical between the broken and correct versions — it's the question being asked. "Give me one number per group" and "give me every row, with a group-level number attached" are different requests, and only one of them is what GROUP BY was built to answer. Before reaching for GROUP BY on a query that also needs individual rows, it's worth checking whether the actual requirement quietly needs OVER (PARTITION BY ...) instead.