Using PARTITION BY to split data into groups

Imagine you're working as a waiter (or a barista, if you're into coffee) in a big restaurant. Every day, you sum up your tips. But here's the thing: the restaurant is split into zones, and you're curious how much tips you made in each zone separately. PARTITION BY is what SQL uses to "split the restaurant into zones."

More formally, PARTITION BY is used in window functions to split all the rows in a table into separate groups (or "partitions"). Inside each group, the window function runs again. It's like you're applying the function separately in each "partition."

Example: how it works

Let's say we have a sales table with sales data:

If we want to calculate how much money each salesperson made, but separately for each region, PARTITION BY is exactly what we need.

Syntax of PARTITION BY

The syntax is pretty simple:

window_function() OVER (PARTITION BY column_or_columns)

• window_function() — for example, SUM(), AVG(), ROW_NUMBER(), and so on.
• PARTITION BY column — tells SQL which column to use to split the rows.
• OVER() — this operator tells SQL: "Do something within the specified window."

Example: sum by groups

Let's calculate the total sales for each region:

SELECT
    region,
    salesperson,
    amount,
    SUM(amount) OVER (PARTITION BY region) AS total_sales_by_region
FROM sales;

The result will look like this:

What's happening here? SQL splits the rows into groups by the region column (North and South), then applies the SUM() function separately for each group. As a result, the rows inside the "North" group all get the same sum value, and the rows inside the "South" group get another.

Examples of using PARTITION BY

Let's check out how PARTITION BY can be useful in real-world tasks.

Example 1: Ranking inside a group

Suppose we want to rank salespeople within each region by their sales amount. For this, we can use a combo of PARTITION BY and the RANK() function:

SELECT
    region,
    salesperson,
    amount,
    RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS rank_in_region
FROM sales;

Result:

The RANK() function assigns a rank within each region group, starting from 1. Notice that for each group, the ranks start from one.

Example 2: Comparing each value to the group average

Let's say we want to see how much each salesperson made compared to the average in their region. We'll use AVG():

SELECT
    region,
    salesperson,
    amount,
    AVG(amount) OVER (PARTITION BY region) AS avg_sales_by_region,
    amount - AVG(amount) OVER (PARTITION BY region) AS diff_from_avg
FROM sales;

Result:

First, SQL splits the rows into groups by region. Then it calculates the average AVG(amount) for each group. Finally, for each row, it calculates the difference between its value and the average.

Example 3: Row numbering inside a group

Say you want to number all transactions inside each region group. Let's use ROW_NUMBER():

SELECT
    region,
    salesperson,
    amount,
    ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS row_number
FROM sales;

Result:

Comparing with GROUP BY

People often get confused between PARTITION BY and GROUP BY. Let's compare them:

GROUP BY

GROUP BY changes the structure of the output — it turns table rows into aggregates. For example:

SELECT
    region,
    SUM(amount) AS total_sales
FROM sales
GROUP BY region;

Result:

Here we lose info about the salespeople, since the data is aggregated.

PARTITION BY

PARTITION BY, on the other hand, doesn't change the structure. We still see every row, but now we have extra values calculated by group. So, PARTITION BY lets you aggregate without losing details.

Common mistakes when using PARTITION BY

Mistake 1: Forgot PARTITION BY

Sometimes you want to group data, but forget to use PARTITION BY. For example:

SELECT
    region,
    salesperson,
    amount,
    SUM(amount) OVER () AS total_sales
FROM sales;

Result:

Here, SUM(amount) is calculated for the whole table, not separately for each region. If you want to take regions into account, don't forget to specify PARTITION BY region.

Mistake 2: Wrong order in ORDER BY

The order of rows inside the window matters for functions like RANK() or ROW_NUMBER(). Be careful when you use ORDER BY inside OVER().