SQL PARTITION BY: Window Functions Per Group (vs GROUP BY)

PARTITION BY divides rows into groups for a window function. The function is calculated separately inside each group, but, unlike GROUP BY, every row stays in the result. It is how you put a group's total, average or rank next to each individual row.

The example table#

Syntax#

function(...) OVER (PARTITION BY column1, column2, ...)

Group values on every row#

SELECT name, department, salary,
       SUM(salary) OVER (PARTITION BY department) AS dept_total,
       COUNT(*)    OVER (PARTITION BY department) AS dept_size
FROM employees
ORDER BY department, name;

name  | department  | salary | dept_total | dept_size
------+-------------+--------+------------+----------
Meera | Engineering | 81000  | 159000     | 2
Ravi  | Engineering | 78000  | 159000     | 2
John  | Marketing   | 46000  | 46000      | 1
Asha  | Sales       | 52000  | 113000     | 2
Priya | Sales       | 61000  | 113000     | 2

Each row keeps its own salary and also shows the total and size of its department.

PARTITION BY vs GROUP BY#

You can even use several different partitions in one query, which GROUP BY cannot do.

Comparing each row with its group#

Who earns the most in their department, and how far below the top is everyone else?

SELECT name, department, salary,
       MAX(salary) OVER (PARTITION BY department) AS dept_max
FROM employees
ORDER BY department;

name  | department  | salary | dept_max
------+-------------+--------+---------
Ravi  | Engineering | 78000  | 81000
Meera | Engineering | 81000  | 81000
John  | Marketing   | 46000  | 46000
Asha  | Sales       | 52000  | 61000
Priya | Sales       | 61000  | 61000

dept_max - salary would give the gap to the top earner in each team.

Shares within a group#

Each region's share of each month's revenue, using monthly_sales:

SELECT month, region, revenue,
       ROUND(100.0 * revenue / SUM(revenue) OVER (PARTITION BY month), 1) AS share
FROM monthly_sales
WHERE month IN ('2026-01', '2026-02')
ORDER BY month, region;

month   | region | revenue | share
--------+--------+---------+------
2026-01 | North  | 120     | 57.1
2026-01 | South  | 90      | 42.9
2026-02 | North  | 150     | 62.5
2026-02 | South  | 90      | 37.5

The partition is the month, so the shares in each month add up to 100.

PARTITION BY with ORDER BY#

Adding ORDER BY inside OVER turns the calculation into a running one that restarts for each partition:

SUM(revenue) OVER (PARTITION BY region ORDER BY month) AS running_total

And ranking functions use both: PARTITION BY decides where numbering restarts, ORDER BY decides the order. See ROW_NUMBER and RANK.

Partitioning by several columns#

PARTITION BY region, EXTRACT(YEAR FROM month_date) creates a separate window for each region-and-year combination, just like grouping by two columns.

Common mistakes#

Practice#

SELECT name, department, salary,
       ROUND(100.0 * salary / SUM(salary) OVER (PARTITION BY department), 1) AS pct_of_dept
FROM employees
ORDER BY department, salary DESC;

name  | department  | salary | pct_of_dept
------+-------------+--------+------------
Meera | Engineering | 81000  | 50.9
Ravi  | Engineering | 78000  | 49.1
John  | Marketing   | 46000  | 100
Priya | Sales       | 61000  | 54
Asha  | Sales       | 52000  | 46

Key takeaway#