OVER clause with PARTITION BY in SQL

Introduction

The OVER clause with PARTITION BY helps you group data into parts and perform calculations on each part separately, without losing the original rows.

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

When you need to calculate running totals for each department in a company.

When you want to find the average sales per region while keeping all sales records visible.

When you want to compare each employee's salary to the average salary in their department.

Syntax

SQL

SELECT column1, column2, aggregate_function() OVER (PARTITION BY column_to_group) AS alias_name FROM table_name;

The PARTITION BY clause divides the rows into groups based on the column you choose.

The aggregate function (like SUM, AVG, COUNT) is applied to each group separately.

Examples

Calculate the average salary for each department while showing each employee's salary.

SQL

SELECT employee_id, department_id, salary,
       AVG(salary) OVER (PARTITION BY department_id) AS avg_dept_salary
FROM employees;

Assign ranks to students within each class based on their scores.

SQL

SELECT student_id, class_id, score,
       RANK() OVER (PARTITION BY class_id ORDER BY score DESC) AS class_rank
FROM exam_results;

Calculate total sales per region while listing each order.

SQL

SELECT order_id, region, amount,
       SUM(amount) OVER (PARTITION BY region) AS total_region_sales
FROM sales;

Sample Program

This query creates a sales table, inserts some data, and then calculates total sales per region while showing each order's details.

SQL

CREATE TABLE sales (
  order_id INT,
  region VARCHAR(20),
  amount INT
);

INSERT INTO sales VALUES
(1, 'North', 100),
(2, 'South', 150),
(3, 'North', 200),
(4, 'South', 50),
(5, 'East', 300);

SELECT order_id, region, amount,
       SUM(amount) OVER (PARTITION BY region) AS total_region_sales
FROM sales
ORDER BY order_id;

Important Notes

The OVER clause does not reduce the number of rows returned; it adds extra information per row.

You can use ORDER BY inside OVER() to define the order for ranking or running totals.

PARTITION BY is like grouping, but it keeps all rows visible.

Summary

Use OVER with PARTITION BY to perform calculations on groups without hiding rows.

It helps compare values within groups like departments or classes.

It works with aggregate and ranking functions to add useful info per row.