PARTITION BY for grouping windows in PostgreSQL

Introduction

PARTITION BY helps you split data into groups to do calculations on each group separately, without mixing them up.

When you want to find the rank of students within each class.

When calculating running totals for each department in a company.

When comparing sales figures month by month for each product category.

When you need to find the average salary per job title in a company.

Syntax

PostgreSQL

window_function() OVER (PARTITION BY column1, column2 ORDER BY column3)

The PARTITION BY clause divides rows into groups based on one or more columns.

Calculations like SUM, AVG, ROW_NUMBER work on each group separately.

Examples

This assigns a rank to employees within each department based on salary.

PostgreSQL

SELECT employee_id, department, salary,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;

This calculates a running total of sales for each product category ordered by date.

PostgreSQL

SELECT product_category, sale_date, sale_amount,
       SUM(sale_amount) OVER (PARTITION BY product_category ORDER BY sale_date) AS running_total
FROM sales;

Sample Program

This example creates an employees table, inserts sample data, and then ranks employees by salary within each department.

PostgreSQL

CREATE TABLE employees (
  employee_id SERIAL PRIMARY KEY,
  name TEXT,
  department TEXT,
  salary INT
);

INSERT INTO employees (name, department, salary) VALUES
('Alice', 'HR', 5000),
('Bob', 'HR', 6000),
('Charlie', 'IT', 7000),
('David', 'IT', 6500),
('Eve', 'HR', 5500);

SELECT name, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
FROM employees
ORDER BY department, salary_rank;

Important Notes

PARTITION BY does not reduce the number of rows returned; it just groups rows for window functions.

You can use multiple columns in PARTITION BY to create more specific groups.

Summary

PARTITION BY splits data into groups for window functions.

It helps calculate ranks, sums, averages within each group.

Useful for comparing data inside categories without losing detail.