SQL 55 🛢️ Partitioning Data with PARTITION BY
The PARTITION BY clause is the part of a window function that divides the rows into groups. Without it, the window is the entire result set. With it, the window is each group, and the function is computed independently within each group. This is what makes “the average salary of the department, shown next to each employee” possible: the PARTITION BY department divides the employees into departments, and the AVG(salary) is computed per department, not over all employees.
This chapter covers PARTITION BY in full. You will learn the syntax, the difference from GROUP BY, the behavior with multiple columns, the interaction with ORDER BY and the frame, and the patterns that use partitioning for ranking, running totals, percentages, and top-N queries.
Key point: PARTITION BY is to a window function what GROUP BY is to an aggregate, with one difference: GROUP BY collapses the rows into one row per group, and PARTITION BY keeps all the rows. The partition is the window. The function is computed within each partition. The partitions are not output as separate rows; they are the scope in which the function operates.
Why PARTITION BY matters
The per-group problem. A window function without PARTITION BY computes over the entire result set. Every row gets the same value. This is useful for “percentage of total” but not for “percentage of department.” The PARTITION BY is the clause that scopes the function to a group.
The comparison problem. “Each employee compared to their department average” requires the average to be computed per department. The AVG(salary) OVER (PARTITION BY department) provides it. The GROUP BY version would collapse the employees and lose the individual rows.
The ranking problem. “The top three products per category” requires the rank to be computed per category. The ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) assigns a rank within each category. Without the PARTITION BY, the rank would be over all products.
The running total problem. “Cumulative sales by customer” requires the sum to accumulate within each customer’s orders. The PARTITION BY customer_id ORDER BY order_date scopes the running total to each customer.
The percentage problem. “Each product’s share of its category’s total” requires the total to be computed per category. The SUM(price) OVER (PARTITION BY category) provides the denominator, and the product’s price divided by it is the share.
The difference from GROUP BY. The two clauses look similar and serve similar purposes, but they produce different results. The GROUP BY reduces the rows to one per group. The PARTITION BY keeps all the rows and attaches the computed value. Knowing when to use each is knowing what the query needs to return.
a. The PARTITION BY syntax and the difference from GROUP BY
The PARTITION BY clause appears inside the OVER parentheses.
SELECT
employee_id,
department,
salary,
AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;The PARTITION BY department divides the employees into departments. The AVG(salary) is computed within each department. The result has one row per employee, with the department average attached.
The GROUP BY version produces a different result.
SELECT
department,
AVG(salary) AS dept_avg
FROM employees
GROUP BY department;The result has one row per department. The individual employees are gone. The AVG is the same value, but the rows are collapsed.
The difference:
| Aspect | GROUP BY | PARTITION BY |
|---|---|---|
| Rows output | One per group | One per input row |
| Individual rows | Lost | Preserved |
| Can mix with aggregates | Yes | Yes |
| Can mix with window functions | No | Yes |
| Purpose | Summarize | Attach per-group values |
The PARTITION BY can be used with any window function. The aggregate functions (SUM, AVG, MIN, MAX, COUNT) and the dedicated window functions (ROW_NUMBER, RANK, LAG, LEAD) all accept it.
SELECT
employee_id,
department,
salary,
AVG(salary) OVER (PARTITION BY department) AS dept_avg,
MAX(salary) OVER (PARTITION BY department) AS dept_max,
COUNT(*) OVER (PARTITION BY department) AS dept_count
FROM employees;Each function is computed per department. The result has one row per employee, with three per-department values attached.
The PARTITION BY can be combined with GROUP BY in the same query. The GROUP BY produces the summary rows, and the window function computes over those rows.
SELECT
department,
AVG(salary) AS dept_avg,
AVG(AVG(salary)) OVER () AS overall_avg
FROM employees
GROUP BY department;The inner AVG(salary) is the GROUP BY aggregate. The outer AVG(AVG(salary)) OVER () is the window function over the group rows. The result has one row per department, with the department average and the overall average.
b. Multiple partition columns and the interaction with ORDER BY
The PARTITION BY accepts multiple columns. The rows are divided by the combination.
SELECT
employee_id,
department,
location,
salary,
AVG(salary) OVER (PARTITION BY department, location) AS dept_loc_avg
FROM employees;The partitions are the unique combinations of department and location. The average is computed for each combination. An employee in Engineering in New York is in a different partition from an employee in Engineering in London.
The ORDER BY within the window orders the rows within each partition. It is required for ranking functions and for running totals.
SELECT
employee_id,
department,
salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;The ROW_NUMBER is computed within each department, ordered by salary descending. The highest-paid employee in each department gets 1.
The PARTITION BY and ORDER BY together define the scope and the order. The frame clause defines which rows within the ordered partition are included.
SELECT
order_date,
customer_id,
amount,
SUM(amount) OVER (
PARTITION BY customer_id
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM orders;The running total is per customer, ordered by date, from the start of the customer’s orders to the current row.
The PARTITION BY without an ORDER BY computes over the entire partition.
SELECT
employee_id,
department,
salary,
SUM(salary) OVER (PARTITION BY department) AS dept_total
FROM employees;The SUM is the total for the department, and every employee in the department gets the same value. The result is the same as the GROUP BY sum, but with the individual rows preserved.
The PARTITION BY with an ORDER BY and no frame uses the default frame, which is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. This produces a running aggregate, not the partition total.
SELECT
order_date,
customer_id,
amount,
SUM(amount) OVER (
PARTITION BY customer_id
ORDER BY order_date
) AS running_total
FROM orders;The SUM is the running total per customer, from the first order to the current one. It is not the customer’s total. To get the customer’s total, the frame must be ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING or the ORDER BY must be omitted.
c. Patterns using PARTITION BY
Ranking within a group.
SELECT
employee_id,
department,
salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;Each employee’s rank within their department. Ties share the rank.
Top N per group.
SELECT * FROM (
SELECT
product_id,
category,
price,
ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS rn
FROM products
) t
WHERE rn <= 3;The top three products per category. The inner query assigns the rank, and the outer query filters.
Percentage of group total.
SELECT
product_id,
category,
price,
100.0 * price / SUM(price) OVER (PARTITION BY category) AS pct_of_category
FROM products;Each product’s share of its category’s total.
Running total per group.
SELECT
order_date,
customer_id,
amount,
SUM(amount) OVER (
PARTITION BY customer_id
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM orders;The cumulative amount per customer over time.
Difference from the previous row in the group.
SELECT
order_date,
customer_id,
amount,
amount - LAG(amount) OVER (
PARTITION BY customer_id ORDER BY order_date
) AS change
FROM orders;The change from the previous order for each customer.
First and last in the group.
SELECT
employee_id,
department,
salary,
FIRST_VALUE(salary) OVER (
PARTITION BY department ORDER BY salary DESC
) AS highest,
LAST_VALUE(salary) OVER (
PARTITION BY department ORDER BY salary DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS lowest
FROM employees;The FIRST_VALUE gives the highest salary in the department, and the LAST_VALUE gives the lowest. The LAST_VALUE requires the frame to include all rows, because the default frame stops at the current row.
Count of rows in the group.
SELECT
employee_id,
department,
COUNT(*) OVER (PARTITION BY department) AS dept_size
FROM employees;The number of employees in each department, attached to each employee row.
Average of the group.
SELECT
employee_id,
department,
salary,
AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;The department average, attached to each employee.
Complete Example Session
-- ============================================
-- PART 1: BASIC PARTITION BY
-- ============================================
SELECT
employee_id,
department,
salary,
AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;-- ============================================
-- PART 2: PARTITION WITH MULTIPLE COLUMNS
-- ============================================
SELECT
employee_id,
department,
location,
salary,
AVG(salary) OVER (PARTITION BY department, location) AS avg
FROM employees;-- ============================================
-- PART 3: PARTITION WITH ORDER BY
-- ============================================
SELECT
employee_id,
department,
salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;-- ============================================
-- PART 4: RUNNING TOTAL PER PARTITION
-- ============================================
SELECT
order_date,
customer_id,
amount,
SUM(amount) OVER (
PARTITION BY customer_id
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM orders;-- ============================================
-- PART 5: PERCENTAGE OF PARTITION TOTAL
-- ============================================
SELECT
product_id,
category,
price,
100.0 * price / SUM(price) OVER (PARTITION BY category) AS pct
FROM products;-- ============================================
-- PART 6: TOP N PER PARTITION
-- ============================================
SELECT * FROM (
SELECT
product_id,
category,
price,
ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS rn
FROM products
) t
WHERE rn <= 3;-- ============================================
-- PART 7: LAG WITH PARTITION
-- ============================================
SELECT
order_date,
customer_id,
amount,
LAG(amount) OVER (
PARTITION BY customer_id ORDER BY order_date
) AS prev_amount
FROM orders;-- ============================================
-- PART 8: FIRST AND LAST VALUE
-- ============================================
SELECT
employee_id,
department,
salary,
FIRST_VALUE(salary) OVER (
PARTITION BY department ORDER BY salary DESC
) AS highest,
LAST_VALUE(salary) OVER (
PARTITION BY department ORDER BY salary DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS lowest
FROM employees;-- ============================================
-- PART 9: COUNT OVER PARTITION
-- ============================================
SELECT
employee_id,
department,
COUNT(*) OVER (PARTITION BY department) AS dept_size
FROM employees;-- ============================================
-- PART 10: PARTITION WITHOUT ORDER BY
-- ============================================
SELECT
employee_id,
department,
salary,
SUM(salary) OVER (PARTITION BY department) AS dept_total
FROM employees;The ten parts covered basic PARTITION BY, multiple columns, ORDER BY, a running total, percentage of total, top-N per partition, LAG, FIRST_VALUE and LAST_VALUE, COUNT, and SUM without ORDER BY.
Quick Reference
PARTITION BY Syntax
| Part | Purpose |
|---|---|
PARTITION BY col | Divide by one column |
PARTITION BY col1, col2 | Divide by multiple columns |
PARTITION BY expr | Divide by an expression |
No PARTITION BY | Single window over all rows |
PARTITION BY vs GROUP BY
| Aspect | GROUP BY | PARTITION BY |
|---|---|---|
| Rows output | One per group | One per input row |
| Individual rows | Lost | Preserved |
| Placement | After FROM | Inside OVER |
| Can mix with window | No | Yes |
| Aggregates | Yes | Yes |
Common Patterns
| Pattern | Window Function |
|---|---|
| Group average | AVG(x) OVER (PARTITION BY g) |
| Group total | SUM(x) OVER (PARTITION BY g) |
| Group count | COUNT(*) OVER (PARTITION BY g) |
| Rank in group | ROW_NUMBER() OVER (PARTITION BY g ORDER BY x) |
| Running total | SUM(x) OVER (PARTITION BY g ORDER BY d) |
| Percentage of group | 100.0 * x / SUM(x) OVER (PARTITION BY g) |
| Previous in group | LAG(x) OVER (PARTITION BY g ORDER BY d) |
| Top N per group | ROW_NUMBER() OVER (PARTITION BY g ORDER BY x) + filter |
Partition with ORDER BY
| Clause | Default Frame |
|---|---|
PARTITION BY g | All rows in partition |
PARTITION BY g ORDER BY d | Running from start to current |
PARTITION BY g ORDER BY d ROWS BETWEEN ... | Explicit frame |
Best Practices
✅ Do This:
-- Use PARTITION BY for per-group calculations
AVG(salary) OVER (PARTITION BY department) -- ✅
-- Use multiple columns for finer partitions
AVG(salary) OVER (PARTITION BY department, location) -- ✅
-- Use ORDER BY with ranking functions
ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) -- ✅
-- Specify the frame for running totals
SUM(amount) OVER (PARTITION BY customer
ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) -- ✅
-- Use FIRST_VALUE and LAST_VALUE with the full frame
FIRST_VALUE(x) OVER (PARTITION BY g ORDER BY d
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) -- ✅
-- Use a subquery to filter on window functions
SELECT * FROM (SELECT ..., ROW_NUMBER() OVER (...) AS rn) t
WHERE rn <= 3; -- ✅❌ Don’t Do This:
-- Don't confuse PARTITION BY with GROUP BY
SELECT department, AVG(salary) OVER (PARTITION BY department)
FROM employees GROUP BY department; -- collapses rows -- ⚠️
-- Don't use a window function in WHERE
WHERE ROW_NUMBER() OVER (PARTITION BY g ORDER BY x) = 1 -- ❌
-- Don't forget the frame for LAST_VALUE
LAST_VALUE(x) OVER (PARTITION BY g ORDER BY d) -- returns current -- ⚠️
-- Don't use PARTITION BY when the whole set is the scope
AVG(x) OVER (PARTITION BY 1) -- use OVER () instead -- ⚠️
-- Don't expect the partition to appear in the output
-- The partition is the window, not a group -- ⚠️
-- Don't use RANK when ROW_NUMBER is needed
RANK() OVER (PARTITION BY g ORDER BY x) -- ties -- ⚠️Common Pitfalls
| Pitfall | Why It Happens | Fix |
|---|---|---|
Window function in WHERE | Not allowed | Wrap in subquery |
LAST_VALUE returns current | Default frame stops at current | Add ROWS BETWEEN ... AND UNBOUNDED FOLLOWING |
| Wrong partition scope | Missing or wrong column | Check the PARTITION BY |
| Running total instead of total | ORDER BY present | Remove ORDER BY or use full frame |
| Ties in ranking | ROW_NUMBER vs RANK | Choose deliberately |
| Partition column not indexed | Performance | Index the partition column |
Confusion with GROUP BY | Similar purpose | GROUP BY collapses, PARTITION BY does not |
Real-World Examples
1. Department Average
SELECT employee_id, department, salary,
AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;2. Rank in Department
SELECT employee_id, department, salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;3. Top 3 per Category
SELECT * FROM (
SELECT product_id, category, price,
ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS rn
FROM products
) t WHERE rn <= 3;4. Running Total per Customer
SELECT order_date, customer_id, amount,
SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running
FROM orders;5. Percentage of Category
SELECT product_id, category, price,
100.0 * price / SUM(price) OVER (PARTITION BY category) AS pct
FROM products;6. Previous Order Amount
SELECT order_date, customer_id, amount,
LAG(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev
FROM orders;7. First and Last in Group
SELECT employee_id, department, salary,
FIRST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS top,
LAST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS bottom
FROM employees;8. Group Size
SELECT employee_id, department,
COUNT(*) OVER (PARTITION BY department) AS dept_size
FROM employees;9. Employees Above Department Average
SELECT * FROM (
SELECT employee_id, department, salary,
AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees
) t WHERE salary > dept_avg;10. Monthly Total per Customer
SELECT customer_id, DATE_TRUNC('month', order_date) AS month,
SUM(amount) OVER (PARTITION BY customer_id,
DATE_TRUNC('month', order_date)) AS monthly_total
FROM orders;Visual
PARTITION BY Concept
┌─────────────────────────────────────────────────────────────┐
│ FULL TABLE │
│ ┌──────┬────────────┬────────┐ │
│ │ id │ department │ salary │ │
│ ├──────┼────────────┼────────┤ │
│ │ 1 │ Eng │ 95000 │ │
│ │ 2 │ Eng │ 85000 │ │
│ │ 3 │ Sales │ 75000 │ │
│ │ 4 │ Sales │ 65000 │ │
│ └──────┴────────────┴────────┘ │
│ │
│ PARTITION BY department │
│ │
│ ┌─────────────────────────┐ ┌─────────────────────────┐ │
│ │ Eng │ │ Sales │ │
│ │ 1 95000 │ │ 3 75000 │ │
│ │ 2 85000 │ │ 4 65000 │ │
│ │ │ │ │ │
│ │ AVG = 90000 │ │ AVG = 70000 │ │
│ └─────────────────────────┘ └─────────────────────────┘ │
│ │
│ The function is computed per partition. │
│ The result keeps all rows. │
│ │
└─────────────────────────────────────────────────────────────┘GROUP BY vs PARTITION BY
┌─────────────────────────────────────────────────────────────┐
│ GROUP BY department │
│ │
│ ┌────────────┬──────────┐ │
│ │ department │ avg │ │
│ ├────────────┼──────────┤ │
│ │ Eng │ 90000 │ │
│ │ Sales │ 70000 │ │
│ └────────────┴──────────┘ │
│ │
│ 2 rows. Employees are gone. │
│ │
├─────────────────────────────────────────────────────────────┤
│ │
│ PARTITION BY department │
│ │
│ ┌──────┬────────────┬────────┬───────┐ │
│ │ id │ department │ salary │ avg │ │
│ ├──────┼────────────┼────────┼───────┤ │
│ │ 1 │ Eng │ 95000 │ 90000 │ │
│ │ 2 │ Eng │ 85000 │ 90000 │ │
│ │ 3 │ Sales │ 75000 │ 70000 │ │
│ │ 4 │ Sales │ 65000 │ 70000 │ │
│ └──────┴────────────┴────────┴───────┘ │
│ │
│ 4 rows. Employees preserved. │
│ │
└─────────────────────────────────────────────────────────────┘Running Total per Partition
┌─────────────────────────────────────────────────────────────┐
│ PARTITION BY customer_id ORDER BY order_date │
│ │
│ Customer A: │
│ ┌────────────┬────────┬───────────────┐ │
│ │ order_date │ amount │ running_total │ │
│ ├────────────┼────────┼───────────────┤ │
│ │ 2026-01-01 │ 100 │ 100 │ │
│ │ 2026-01-05 │ 200 │ 300 │ │
│ │ 2026-01-10 │ 150 │ 450 │ │
│ └────────────┴────────┴───────────────┘ │
│ │
│ Customer B: │
│ ┌────────────┬────────┬───────────────┐ │
│ │ order_date │ amount │ running_total │ │
│ ├────────────┼────────┼───────────────┤ │
│ │ 2026-01-02 │ 50 │ 50 │ │
│ │ 2026-01-08 │ 75 │ 125 │ │
│ └────────────┴────────┴───────────────┘ │
│ │
│ Each customer's running total is independent. │
│ │
└─────────────────────────────────────────────────────────────┘Top N per Partition
┌─────────────────────────────────────────────────────────────┐
│ INNER: ROW_NUMBER() OVER (PARTITION BY category ORDER BY) │
│ │
│ ┌──────┬──────────┬───────┬────┐ │
│ │ id │ category │ price │ rn │ │
│ ├──────┼──────────┼───────┼────┤ │
│ │ 1 │ A │ 100 │ 1 │ │
│ │ 2 │ A │ 90 │ 2 │ │
│ │ 3 │ A │ 80 │ 3 │ │
│ │ 4 │ B │ 200 │ 1 │ │
│ │ 5 │ B │ 150 │ 2 │ │
│ │ 6 │ B │ 120 │ 3 │ │
│ └──────┴──────────┴───────┴────┘ │
│ │
│ OUTER: WHERE rn <= 3 │
│ │
│ The top 3 products per category. │
│ │
└─────────────────────────────────────────────────────────────┘Summary
| Item | Value |
|---|---|
| Clause | PARTITION BY col |
| Placement | Inside OVER |
| Purpose | Divide rows into groups |
| Rows output | One per input row |
| Multiple columns | PARTITION BY a, b |
With ORDER BY | Order within partition |
Without ORDER BY | Entire partition |
| With frame | Define subset |
vs GROUP BY | Preserves rows |
| Common use | Rank, running total, percentage |
Key takeaways:
PARTITION BYdivides the rows into groups for a window function. The function is computed independently within each group. The rows are preserved; the partition is the scope, not the output.PARTITION BYdiffers fromGROUP BYin the output.GROUP BYcollapses the rows to one per group.PARTITION BYkeeps all the rows and attaches the computed value. The choice depends on whether the individual rows are needed.- Multiple columns create finer partitions.
PARTITION BY department, locationdivides by the combination. Each unique combination is a partition. - The
ORDER BYwithin the window orders the rows within the partition. It is required for ranking functions and for running totals. Without it, the entire partition is the scope. - The default frame with an
ORDER BYis a running aggregate. It includes all rows from the start of the partition to the current row. To get the partition total, remove theORDER BYor specify a full frame. LAST_VALUErequires the full frame. The default frame stops at the current row, soLAST_VALUEreturns the current row’s value. UseROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWINGto get the actual last value.- Window functions cannot be used in
WHERE. They are evaluated after theWHEREclause. To filter on a window function, wrap the query in a subquery and filter in the outer query. This is the pattern for top-N per group.
Remember: PARTITION BY is the clause that scopes a window function to a group. It is the answer to “per group” questions that GROUP BY cannot answer because they need the individual rows. Use it for rankings, running totals, percentages, top-N, and row comparisons. Combine it with ORDER BY for ordered calculations and with the frame clause for the subset. And remember the difference from GROUP BY: the partition keeps the rows, the group collapses them. The choice is the difference between “each row and its group’s value” and “the group’s value.”
Stop using slow, ad-bloated tool sites! 🤮
🔎 Search “KandZ Tools” on Google to use many professional utilities for free.
KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)
⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free
🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!