PARTITION BY in SQL

đŸĒŸ The PARTITION BY clause is used with SQL window functions to divide a result set into logical groups (partitions). Each partition is processed independently, allowing calculations such as rankings, running totals, averages, and comparisons to restart for every group while still returning every row.

📖 What is PARTITION BY?

PARTITION BY is part of the OVER() clause used by window functions. Instead of combining rows like GROUP BY, it keeps every row in the result set and performs calculations separately within each partition.

Information

Think of PARTITION BY as creating multiple independent windows. Each window is processed separately, but all rows remain visible in the final output.

đŸŽ¯ Why Use PARTITION BY?

PARTITION BY makes analytical queries more flexible by allowing calculations within specific groups.

  • 📌 Restart rankings for each group.
  • 📌 Calculate department-wise averages.
  • 📌 Generate running totals per category.
  • 📌 Compare rows within the same group.
  • 📌 Preserve every row in the result.

📋 Sample Table

EmployeeIDEmployeeNameDepartmentSalary
101AliceSales70000
102BobSales65000
103CharlieIT80000
104DavidIT75000
105EmmaHR60000

📝 Basic Syntax

PARTITION BY Syntax

SELECT
    column_name,
    window_function() OVER (
        PARTITION BY partition_column
        ORDER BY order_column
    ) AS result
FROM table_name;

The PARTITION BY clause defines how rows are grouped, while ORDER BY determines the processing order within each partition.

💡 Example: Rank Employees Within Each Department

ROW_NUMBER() with PARTITION BY

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

The row numbering restarts from 1 for every department.

📊 Example Output

EmployeeDepartmentSalaryDepartmentRank
AliceSales700001
BobSales650002
CharlieIT800001
DavidIT750002
EmmaHR600001

💡 Example: Department Average Salary

AVG() with PARTITION BY

SELECT
    EmployeeName,
    Department,
    Salary,
    AVG(Salary) OVER (
        PARTITION BY Department
    ) AS DepartmentAverage
FROM Employees;

Every employee row displays the average salary of its department while keeping all rows in the result.

📊 Example Output

EmployeeDepartmentSalaryDepartmentAverage
AliceSales7000067500
BobSales6500067500
CharlieIT8000077500
DavidIT7500077500
EmmaHR6000060000

💡 Example: Running Total Per Department

SUM() with PARTITION BY

SELECT
    EmployeeName,
    Department,
    Salary,
    SUM(Salary) OVER (
        PARTITION BY Department
        ORDER BY Salary
    ) AS RunningTotal
FROM Employees;

The running total starts over whenever the department changes.

💡 Example: Compare with Previous Salary

LAG() with PARTITION BY

SELECT
    EmployeeName,
    Department,
    Salary,
    LAG(Salary) OVER (
        PARTITION BY Department
        ORDER BY Salary
    ) AS PreviousSalary
FROM Employees;

Each employee is compared only with previous employees in the same department.

📚 Common Window Functions That Use PARTITION BY

FunctionTypical Purpose
ROW_NUMBER()Unique numbering within each partition.
RANK()Ranking with gaps.
DENSE_RANK()Ranking without gaps.
LAG()Access previous row in a partition.
LEAD()Access next row in a partition.
SUM()Running totals by group.
AVG()Group averages.
COUNT()Count rows in each partition.

⚖ī¸ PARTITION BY vs GROUP BY

PARTITION BYGROUP BY
Keeps every row.Returns one row per group.
Used with window functions.Used with aggregate functions.
Performs calculations within groups.Summarizes each group.
Does not collapse rows.Collapses rows into grouped results.

Important

Although both clauses group data logically, GROUP BY reduces the number of rows returned, while PARTITION BY preserves every row.

đŸ’ŧ Real-World Applications

  • đŸĸ Rank employees within each department.
  • 📈 Calculate regional sales totals.
  • đŸĻ Analyze account transactions per customer.
  • 🛒 Compare product performance within each category.
  • 🎓 Rank students within each class.
  • 📊 Generate business intelligence reports.

🗄ī¸ Database Compatibility

Database SystemPARTITION BY Support
MySQL✅ Supported in MySQL 8.0 and later.
PostgreSQL✅ Fully supported.
SQL Server✅ Fully supported.
Oracle✅ Fully supported.
SQLite✅ Supported in SQLite 3.25.0 and later.

⚠ī¸ Common Mistakes

  • ❌ Confusing PARTITION BY with GROUP BY.
  • ❌ Forgetting ORDER BY when row sequence affects the calculation.
  • ❌ Partitioning by the wrong column.
  • ❌ Assuming partitions change the number of returned rows.

Warning

PARTITION BY only divides rows for window function calculations. It does not filter rows or aggregate them into fewer results.

⚠ī¸ Best Practices

Best Practice

Use PARTITION BY whenever calculations should restart for each logical group, pair it with ORDER BY when row order matters, choose partition columns carefully based on business requirements, and use descriptive aliases for calculated columns to improve readability.

🚀 Key Points to Remember

  • 📌 PARTITION BY divides rows into independent groups.
  • 📌 It is used inside the OVER() clause.
  • 📌 It works with SQL window functions.
  • 📌 Every row remains in the final result.
  • 📌 It differs from GROUP BY, which aggregates rows.
  • 📌 It is essential for rankings, running totals, comparisons, and analytical reporting.
>>"PARTITION BY lets SQL analyze groups without hiding the details of individual rows."

Summary

✅ PARTITION BY is a fundamental part of SQL window functions. It divides a result set into logical groups so calculations such as rankings, averages, running totals, and row comparisons can be performed independently within each group while preserving every row in the output. It is one of the most important features for analytical SQL queries.