// SQL
Window function vs GROUP BY in SQL: when to use each
Every example on this page was run on 19 November 2026 in the SQL database on this page, which is close to PostgreSQL, and the output under each one is what it printed. How we test these posts.
The difference between a window function and GROUP BY in SQL is the number of rows you get back. GROUP BY dept folds every row of a department into one row, so you see totals and lose the detail. SUM(salary) OVER (PARTITION BY dept) computes the same total but keeps every row, and writes the total next to each one. Use GROUP BY for a summary, and a window function when each row needs its group’s figure.
What is the difference between a window function and GROUP BY?
GROUP BY returns one row per group. Five employees in two departments become two rows, one per department, and each row holds the aggregates you asked for:
WITH emp(name, dept, salary) AS (
VALUES ('Ava', 'Sales', 5000), ('Ben', 'Sales', 4000),
('Cara', 'Sales', 3000), ('Dev', 'Tech', 7000),
('Eli', 'Tech', 6000)
)
SELECT dept, SUM(salary) AS dept_total, COUNT(*) AS people
FROM emp
GROUP BY dept
ORDER BY dept;
WITH emp(name, dept, salary) AS (
VALUES ('Ava', 'Sales', 5000), ('Ben', 'Sales', 4000),
('Cara', 'Sales', 3000), ('Dev', 'Tech', 7000),
('Eli', 'Tech', 6000)
)
SELECT dept, SUM(salary) AS dept_total, COUNT(*) AS people
FROM emp
GROUP BY dept
ORDER BY dept;Output
| dept | dept_total | people |
|---|---|---|
| Sales | 12000 | 3 |
| Tech | 13000 | 2 |
A window function returns one row per input row. The same SUM(salary) with OVER (PARTITION BY dept) gives all five employees back, each with the total of their own department:
WITH emp(name, dept, salary) AS (
VALUES ('Ava', 'Sales', 5000), ('Ben', 'Sales', 4000),
('Cara', 'Sales', 3000), ('Dev', 'Tech', 7000),
('Eli', 'Tech', 6000)
)
SELECT name, dept, salary,
SUM(salary) OVER (PARTITION BY dept) AS dept_total
FROM emp
ORDER BY dept, name;
WITH emp(name, dept, salary) AS (
VALUES ('Ava', 'Sales', 5000), ('Ben', 'Sales', 4000),
('Cara', 'Sales', 3000), ('Dev', 'Tech', 7000),
('Eli', 'Tech', 6000)
)
SELECT name, dept, salary,
SUM(salary) OVER (PARTITION BY dept) AS dept_total
FROM emp
ORDER BY dept, name;Output
| name | dept | salary | dept_total |
|---|---|---|---|
| Ava | Sales | 5000 | 12000 |
| Ben | Sales | 4000 | 12000 |
| Cara | Sales | 3000 | 12000 |
| Dev | Tech | 7000 | 13000 |
| Eli | Tech | 6000 | 13000 |
Pick GROUP BY when you want one row per group, and a window function when you want every row plus its group’s figure.
What is the difference between PARTITION BY and GROUP BY?
PARTITION BY splits the rows into groups for a window function only, and GROUP BY splits them for the whole query. Both decide which rows belong together, so PARTITION BY dept and GROUP BY dept form the same departments. The difference is what happens next: GROUP BY collapses each department to one row, and PARTITION BY leaves the rows alone and hands each one the result for its partition.
That is also why a GROUP BY query rejects SELECT name, dept, SUM(salary) ... GROUP BY dept: a department has several names, and the row for it can only hold one. That error, and its fixes, are in column must appear in the GROUP BY clause. A window function has no such rule, since no rows are merged.
When should I use a window function instead of GROUP BY?
Use a window function when a row has to be compared with its group: a share of the department total, a gap to the department average, a rank, or a running total. With GROUP BY you would need a second query and a join. Here each salary is shown as a percentage of its department’s payroll:
WITH emp(name, dept, salary) AS (
VALUES ('Ava', 'Sales', 5000), ('Ben', 'Sales', 4000),
('Cara', 'Sales', 3000), ('Dev', 'Tech', 7000),
('Eli', 'Tech', 6000)
)
SELECT name, dept, salary,
ROUND(100.0 * salary / SUM(salary) OVER (PARTITION BY dept), 1) AS pct_of_dept
FROM emp
ORDER BY dept, name;
WITH emp(name, dept, salary) AS (
VALUES ('Ava', 'Sales', 5000), ('Ben', 'Sales', 4000),
('Cara', 'Sales', 3000), ('Dev', 'Tech', 7000),
('Eli', 'Tech', 6000)
)
SELECT name, dept, salary,
ROUND(100.0 * salary / SUM(salary) OVER (PARTITION BY dept), 1) AS pct_of_dept
FROM emp
ORDER BY dept, name;Output
| name | dept | salary | pct_of_dept |
|---|---|---|---|
| Ava | Sales | 5000 | 41.7 |
| Ben | Sales | 4000 | 33.3 |
| Cara | Sales | 3000 | 25 |
| Dev | Tech | 7000 | 53.8 |
| Eli | Tech | 6000 | 46.2 |
Writing 100.0 instead of 100 makes the division decimal. With two integers, PostgreSQL divides as integers and every share under the whole total rounds down to 0.
Ranking and running totals work the same way, with an ORDER BY inside OVER. They have their own posts: RANK vs DENSE_RANK vs ROW_NUMBER and running total in SQL.
How do I get the same result without a window function?
Without a window function, compute the totals with GROUP BY in a subquery and join them back to the rows. The answer is the same as the PARTITION BY version, and it runs on databases with no window functions, such as MySQL 5.7:
WITH emp(name, dept, salary) AS (
VALUES ('Ava', 'Sales', 5000), ('Ben', 'Sales', 4000),
('Cara', 'Sales', 3000), ('Dev', 'Tech', 7000),
('Eli', 'Tech', 6000)
),
totals AS (
SELECT dept, SUM(salary) AS dept_total
FROM emp
GROUP BY dept
)
SELECT e.name, e.dept, e.salary, t.dept_total
FROM emp AS e
JOIN totals AS t ON t.dept = e.dept
ORDER BY e.dept, e.name;
WITH emp(name, dept, salary) AS (
VALUES ('Ava', 'Sales', 5000), ('Ben', 'Sales', 4000),
('Cara', 'Sales', 3000), ('Dev', 'Tech', 7000),
('Eli', 'Tech', 6000)
),
totals AS (
SELECT dept, SUM(salary) AS dept_total
FROM emp
GROUP BY dept
)
SELECT e.name, e.dept, e.salary, t.dept_total
FROM emp AS e
JOIN totals AS t ON t.dept = e.dept
ORDER BY e.dept, e.name;Output
| name | dept | salary | dept_total |
|---|---|---|---|
| Ava | Sales | 5000 | 12000 |
| Ben | Sales | 4000 | 12000 |
| Cara | Sales | 3000 | 12000 |
| Dev | Tech | 7000 | 13000 |
| Eli | Tech | 6000 | 13000 |
The join reads the table twice and repeats the grouping key in two places. The window version says the same thing in one line, which is why interviewers expect it once the question mentions “each row” and “its group”.
Can you use GROUP BY and a window function in the same query?
Yes. GROUP BY runs first, and the window function then runs over the grouped rows. A window function can even wrap an aggregate: SUM(SUM(salary)) OVER () adds up the department totals, so each department row also shows its share of the whole company:
WITH emp(name, dept, salary) AS (
VALUES ('Ava', 'Sales', 5000), ('Ben', 'Sales', 4000),
('Cara', 'Sales', 3000), ('Dev', 'Tech', 7000),
('Eli', 'Tech', 6000)
)
SELECT dept,
SUM(salary) AS dept_total,
SUM(SUM(salary)) OVER () AS company_total,
ROUND(100.0 * SUM(salary) / SUM(SUM(salary)) OVER (), 1) AS pct_of_company
FROM emp
GROUP BY dept
ORDER BY dept;
WITH emp(name, dept, salary) AS (
VALUES ('Ava', 'Sales', 5000), ('Ben', 'Sales', 4000),
('Cara', 'Sales', 3000), ('Dev', 'Tech', 7000),
('Eli', 'Tech', 6000)
)
SELECT dept,
SUM(salary) AS dept_total,
SUM(SUM(salary)) OVER () AS company_total,
ROUND(100.0 * SUM(salary) / SUM(SUM(salary)) OVER (), 1) AS pct_of_company
FROM emp
GROUP BY dept
ORDER BY dept;Output
| dept | dept_total | company_total | pct_of_company |
|---|---|---|---|
| Sales | 12000 | 25000 | 48 |
| Tech | 13000 | 25000 | 52 |
An empty OVER () makes the whole result one window. Inside a grouped query, the window function can only use columns that are grouped or aggregated, the same rule as the SELECT list.
How do I filter on a window function result?
Filter a window function in an outer query, never in WHERE or HAVING of the query that computes it. SQL runs WHERE, then GROUP BY, then HAVING, and only then window functions, so the value does not exist yet. Wrap the window in a CTE and filter the CTE. Here are the employees paid above their department’s average:
WITH emp(name, dept, salary) AS (
VALUES ('Ava', 'Sales', 5000), ('Ben', 'Sales', 4000),
('Cara', 'Sales', 3000), ('Dev', 'Tech', 7000),
('Eli', 'Tech', 6000)
),
scored AS (
SELECT name, dept, salary,
AVG(salary) OVER (PARTITION BY dept) AS dept_avg
FROM emp
)
SELECT name, dept, salary, dept_avg
FROM scored
WHERE salary > dept_avg
ORDER BY dept, name;
WITH emp(name, dept, salary) AS (
VALUES ('Ava', 'Sales', 5000), ('Ben', 'Sales', 4000),
('Cara', 'Sales', 3000), ('Dev', 'Tech', 7000),
('Eli', 'Tech', 6000)
),
scored AS (
SELECT name, dept, salary,
AVG(salary) OVER (PARTITION BY dept) AS dept_avg
FROM emp
)
SELECT name, dept, salary, dept_avg
FROM scored
WHERE salary > dept_avg
ORDER BY dept, name;Output
| name | dept | salary | dept_avg |
|---|---|---|---|
| Ava | Sales | 5000 | 4000 |
| Dev | Tech | 7000 | 6500 |
To filter whole groups instead, such as departments whose payroll is over 12,500, use GROUP BY with HAVING:
WITH emp(name, dept, salary) AS (
VALUES ('Ava', 'Sales', 5000), ('Ben', 'Sales', 4000),
('Cara', 'Sales', 3000), ('Dev', 'Tech', 7000),
('Eli', 'Tech', 6000)
)
SELECT dept, SUM(salary) AS dept_total
FROM emp
GROUP BY dept
HAVING SUM(salary) > 12500;
WITH emp(name, dept, salary) AS (
VALUES ('Ava', 'Sales', 5000), ('Ben', 'Sales', 4000),
('Cara', 'Sales', 3000), ('Dev', 'Tech', 7000),
('Eli', 'Tech', 6000)
)
SELECT dept, SUM(salary) AS dept_total
FROM emp
GROUP BY dept
HAVING SUM(salary) > 12500;Output
| dept | dept_total |
|---|---|
| Tech | 13000 |
Is a window function slower than GROUP BY?
A window function is not slower by nature, but it returns more rows, and the database usually sorts the rows by the PARTITION BY and ORDER BY columns before computing it. When you only need one row per group, GROUP BY does less work and sends less data back. When you need every row with its group’s figure, the window function avoids the second read of the table that a GROUP BY joined back to it needs. Check your own query with EXPLAIN rather than trusting a rule.
The PostgreSQL tutorial on window functions compares them with aggregates, the PostgreSQL window function call syntax defines OVER and PARTITION BY, and the MySQL 8.0 window function concepts page covers them in MySQL. More questions of this kind are in the SQL interview practice guide.
Related errors
Putting a window function straight into WHERE fails with “window functions are not allowed in WHERE”, and putting an aggregate there fails with “aggregate functions are not allowed in WHERE”. Both come from the order SQL runs its clauses in, and both have their own causes and fixes.