Skip to content
Tutorials Center
My learning

Topics

SQL Reference ยท Window Functions

SQL PARTITION BY clause

Reference for SQL PARTITION BY inside OVER(): split rows into independent groups for window functions, how it differs from GROUP BY, NULL partitions and examples.

PARTITION BY is the part of an OVER() clause that divides the rows into independent groups called partitions. The window function is calculated separately inside each partition, so a running total, a rank or a group average starts over whenever the partition value changes.

It looks like GROUP BY, but it does not collapse rows. Every input row is kept, and each row simply sees only the rows of its own partition. If you leave PARTITION BY out, the entire result set is one partition.

SQL
SELECT first_name, department_id, salary,
       AVG(salary) OVER (PARTITION BY department_id) AS department_average
FROM employees;

Try it

SQL Each row compared with its own department ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

SQL Partitioning by several columns, and by an expression ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The last column counts only the rows left after the WHERE filter (orders of these two customers in that month, not all orders of the month), which is the filtering caveat from the usage notes below.

Syntax

SQL
function(...) OVER (PARTITION BY expression [, expression ...] [ORDER BY ...] [frame])
Part Description
expression A column or any expression. Rows with equal values (all listed expressions together) belong to the same partition.

Notes: the expressions are evaluated per row; there is no alias lookup, so repeat the expression instead of using a SELECT alias. The number of partitions equals the number of distinct value combinations.

Dialect differences

PARTITION BY is standard and works the same way everywhere window functions exist (SQLite 3.25+, PostgreSQL 8.4+, MySQL 8.0+, SQL Server, Oracle).

Behavior SQLite PostgreSQL MySQL SQL Server Oracle
Expressions in PARTITION BY Yes Yes Yes Yes Yes
NULL values form one partition Yes Yes Yes Yes Yes
Use a select-list alias No No No No No
SQL
-- Not portable: aliases cannot be used inside OVER
SELECT substr(ordered_at, 1, 7) AS month,
       COUNT(*) OVER (PARTITION BY month) FROM orders;          -- fails

-- Portable: repeat the expression, or compute it in a subquery first
SELECT month, COUNT(*) OVER (PARTITION BY month)
FROM (SELECT substr(ordered_at, 1, 7) AS month FROM orders) AS m;

Usage notes

  • PARTITION BY is not GROUP BY. GROUP BY returns one row per group. PARTITION BY keeps every row and repeats the group-level result on each. You can combine them: window functions then run over the grouped rows, and they may use the grouped aggregates.
  • NULLs are grouped together. All rows with a NULL partition value fall into one partition, the same way GROUP BY treats them.
  • Several expressions create finer partitions: PARTITION BY customer_id, status has one partition per distinct pair.
  • Partitions are independent. Row numbers, ranks, running totals and LAG() never cross a partition boundary. The first row of a partition has no previous row.
  • Frames never leave the partition. Even UNBOUNDED PRECEDING stops at the partition start.
  • Performance: the database usually sorts by the partition columns, so an index that starts with them can help. Fewer different PARTITION BY lists in one query means fewer sorts.
  • Common mistake: filtering with WHERE before the window changes the partitions. If you want counts over all rows but only some in the output, compute the window in a subquery and filter outside.