PARTITION BY

The PARTITION BY clause in SQL is used with window functions to divide the result set into partitions. Each partition is processed separately, allowing calculations to restart for each group of rows.

Unlike GROUP BY, which aggregates rows into a single result per group, PARTITION BY allows you to perform calculations across subsets of rows while keeping the individual rows visible.

---

Syntax

window_function() OVER (PARTITION BY column_name ORDER BY column_name)

• window_function(): Functions like ROW_NUMBER(), RANK(), SUM(), AVG(), etc.
• PARTITION BY column_name: Divides the data into partitions based on the column.
• ORDER BY column_name: (Optional) Defines the order within each partition.

---

Example 1: Row Number Per Partition

Suppose we have a table Employees:

EmployeeID	Department	Salary
1	Sales	5000
2	Sales	6000
3	HR	4500
4	HR	4700
5	IT	7000

We want to assign a row number for each employee within their department.

SELECT 
    EmployeeID,
    Department,
    Salary,
    ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) AS RowNum
FROM Employees;

Result:

EmployeeID	Department	Salary	RowNum
2	Sales	6000	1
1	Sales	5000	2
4	HR	4700	1
3	HR	4500	2
5	IT	7000	1

Explanation: Each department starts counting rows independently because of PARTITION BY Department.

---

Example 2: Sum Per Partition

If you want the total salary per department while keeping all individual rows:

SELECT 
    EmployeeID,
    Department,
    Salary,
    SUM(Salary) OVER (PARTITION BY Department) AS TotalDeptSalary
FROM Employees;

Result:

EmployeeID	Department	Salary	TotalDeptSalary
1	Sales	5000	11000
2	Sales	6000	11000
3	HR	4500	9200
4	HR	4700	9200
5	IT	7000	7000

Explanation: SUM() calculates the total for each partition but does not remove individual rows.

---

Key Points

• PARTITION BY is used only with window functions.
• It does not reduce the number of rows; it just groups them logically.
• Use ORDER BY within OVER() to define row order in calculations.
• Common window functions include ROW_NUMBER(), RANK(), DENSE_RANK(), SUM(), AVG(), COUNT(), and LEAD()/LAG().

---