###### Common Statistical Operations

# Medians in SQL

Finding the * min*,

*, and*

**max***price of an order in your purchases table is easy. But what about the*

**avg***median*price? Medians are much more difficult for the database to compute, so there usually isn’t a built-in function like the following:

-- not going to work!

selectmedian(price)frompurchases

### Why is Median Hard?

For the standard aggregate functions (* min*,

*,*

**max***, etc.) it’s possible to get the result in a single pass over the data, storing the current*

**count***/*

**min***/*

**max***along the way. If the column is indexed, the minimum or maximum value can be found without looking at any rows!*

**count*** Median* cannot work like that because it’s algorithmically much more complicated. The median is the middle value. The data needs to be sorted so that the middle record can be found.

### Finding the Median with Different Databases

Since median is harder than * min*/

*/*

**max***and not a standard aggregate function, we’ll need to do a little more work to calculate it. Here’s how on several different databases:*

**count**#### Median on Redshift

The Redshift team recently released a median window function, making it one of the easiest syntaxes to find the median with:

selectmedian(price) over ()asmedian

frompurchases

limit1

Note the * limit 1*: Since

*is a window function and not an aggregate function, it’ll return one value for each row in the table.*

**median**#### Median on Postgres

If you like defining your own functions in Postgres, the Postgres Wiki has a definition for median. We’ll do it in SQL and get Postgres to help us find the middle value by numbering all the rows with the * row_number()* window function.

First, a CTE to sort and number all of the rows, with a * count* that’ll help later on:

withordered_purchasesas(

select

price,

row_number() over (orderbyprice)asrow_id,

(selectcount(1)frompurchases)asct

frompurchases

)

Then we find the middle one or two rows and average their values:

selectavg(price)asmedian

fromordered_purchases

whererow_idbetweenct/2.0andct/2.0+1

The * where* clause ensures that we’ll get the two middle values if there is an even number of values, and the single middle number if there is an odd number of values because

*is inclusive of its bounds.*

**between**#### Median on MySQL

MySQL might not have window functions, but it does have variables, and we’ll use them to achieve the same result.

First we’ll set two variables, one for the row count and one to act as the * row_id* from before:

set@ct :=(selectcount(1)frompurchases);

set@row_id :=0;

And just like before, we average the middle one or two values:

selectavg(price)asmedian

from(select*frompurchasesorderbyprice)

where(select@row_id :=@row_id+1)

between@ct/2.0and@ct/2.0+1

The * @row_id := @row_id + 1* syntax simply increments the

*counter for each row. Unlike Postgres we don’t need to build up a temporary result set of rows with*

**@row_id***because variables let us compute the*

**row_id***on the fly.*

**row_id**### Bonus Round: Mode

No post with * avg* and

*is complete without*

**median***! The*

**mode***(defined as the most frequent value) of a column can be found with a simple group and count:*

**mode**selectpriceasmode

frompurchases

groupby1

orderbycount(1)desc

limit1

This query gives us the * price* with the greatest

*in the table, also known as the mode.*

**count()**And now you know how to compute median in SQL for Redshift, Postgres and MySQL databases!