# MySQL: PARTITION BY reference

17 August 2023 (Updated 24 September 2023)

### In a nutshell

The `PARTITION BY`

clause lets you divide the result set of a query into partitions based on one or more columns, and then perform calculations against each partition using window functions like `SUM()`

, `MAX()`

, `AVG()`

.

Here’s an example use of `PARTITION BY`

to find out how much each product category has sold to date.

```
SELECT
Product,
Category,
SaleDate,
SaleAmount,
SUM(SaleAmount) OVER (PARTITION BY Category ORDER BY SaleDate) AS RunningTotal
FROM
Sales;
```

This is what happens as the query is processed.

**Partitioning**. The`PARTITION BY Category`

clause divides the result set into separate partitions where each partition contains only those rows in`Sales`

where the`Category`

column is the same. So you might have one partition for rows where the`Category`

column is`"Electrical"`

, another partition where the`Category`

column is “Gardening”, and so on.**Ordering**. The`ORDER BY SaleDate`

orders the partition rows.**Window Function Calculation**. After partitioning and ordering, the window function (something like`SUM()`

or`AVG(`

) is applied to each row within the partition. The result of the window function will only use the rows within the partition

Tagged:
MySQL