How do window functions differ from GROUP BY?

Detailed explanation

Both operate on groups of rows, but they return different shapes.

• GROUP BY produces one row per group. Every selected column must be grouped or aggregated.
• A window function (... OVER (PARTITION BY ...)) produces one row per input row. The partition only defines which rows feed the calculation.

Sample data

CREATE TABLE employees (name TEXT PRIMARY KEY, dept TEXT NOT NULL, salary INT NOT NULL);
INSERT INTO employees VALUES
  ('Ana', 'eng', 120), ('Bo', 'eng', 100), ('Cy', 'eng', 80),
  ('Di', 'sales', 70), ('Ed', 'sales', 50);

Same aggregate, two shapes

SELECT dept, AVG(salary)::numeric(6,1) AS avg_salary
FROM employees
GROUP BY dept
ORDER BY dept;

dept	avg_salary
eng	100.0
sales	60.0

SELECT name, dept, salary,
       AVG(salary) OVER (PARTITION BY dept)::numeric(6,1)          AS dept_avg,
       salary - AVG(salary) OVER (PARTITION BY dept)::numeric(6,1) AS diff_from_avg,
       RANK() OVER (PARTITION BY dept ORDER BY salary DESC)        AS rank_in_dept
FROM employees
ORDER BY dept, salary DESC;

name	dept	salary	dept_avg	diff_from_avg	rank_in_dept
Ana	eng	120	100.0	20.0	1
Bo	eng	100	100.0	0.0	2
Cy	eng	80	100.0	-20.0	3
Di	sales	70	60.0	10.0	1
Ed	sales	50	60.0	-10.0	2

The second query answers “how does each person compare with their department?”, which GROUP BY alone cannot do without joining the aggregate back to the detail rows.

Both in one query

Window functions are evaluated after GROUP BY and HAVING, so they can work on the grouped result. Each department’s share of the total payroll:

SELECT dept,
       SUM(salary) AS payroll,
       ROUND(100.0 * SUM(salary) / SUM(SUM(salary)) OVER (), 1) AS pct_of_total
FROM employees
GROUP BY dept
ORDER BY dept;

dept	payroll	pct_of_total
eng	300	71.4
sales	120	28.6

SUM(SUM(salary)) OVER () reads as: the inner SUM is the group aggregate, the outer one is a window over all grouped rows.

When to use which

Need	Use
Totals or averages per group	GROUP BY
Rank within a group, top N per group	Window (ROW_NUMBER, RANK, DENSE_RANK)
Running total, moving average	Window with an ORDER BY and a frame
Compare to the previous row	Window (LAG, LEAD)
Share of total on each row	Window (x / SUM(x) OVER (...))

Common mistakes

1. Selecting a non-aggregated column with GROUP BY (an error in standard SQL, unless it is functionally dependent on a grouped primary key, which PostgreSQL allows).
2. Filtering on a window result in WHERE. Window functions are evaluated after WHERE, so filter in an outer query or CTE, or use QUALIFY in Snowflake, BigQuery, Databricks or DuckDB.
3. Adding ORDER BY inside OVER for a group total: SUM(x) OVER (PARTITION BY g ORDER BY d) is a running total, because the default frame then ends at the current row.
4. Saying window functions are “faster”. They solve a different problem; each distinct window specification usually needs a sort.