Calculate median in SQL

OneLearn Course SQL
#1

How to find the median of any Numeric parameter in SQL?

#2

You can calculate the median of any numeric parameter in SQL by using the
PERCENTILE_CONT() and WITHIN GROUP function
WITHIN GROUP() - helps specify a group of values over which some operations will take place
PERCENTILE_CONT() - helps to calculate percentiles over a group of values

Below code shows the creation of a sales table

create table sales(order_date date,sale int);
insert into sales values('2020-04-01',210),
           ('2020-04-02',125),('2020-04-03',150),('2020-04-04',230),
           ('2020-04-05',200),('2020-04-10',220),('2020-04-06',25),
           ('2020-04-07',215),('2020-04-08',300),('2020-04-09',250);
select * from sales;
order_date | sale
------------+------
 2020-04-01 |  210
 2020-04-02 |  125
 2020-04-03 |  150
 2020-04-04 |  230
 2020-04-05 |  200
 2020-04-10 |  220
 2020-04-06 |   25
 2020-04-07 |  215
 2020-04-08 |  300
 2020-04-09 |  250

Here’s the query to find median, that is, calculate 50th percentile in PostgreSQL.

SELECT PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY sale) FROM sales;
percentile_cont
-----------------
           212.5

In the above query, we use percentile_cont function that allows you to define what percentile (e.g 0.5 that is 50th percentile) you want to calculate. Then we also use WITHIN GROUP clause to tell PostgreSQL to calculate percentile within the group of sale values. We also use ORDER BY clause to tell PostgreSQL to sort the sale values before calculating median.

When you use percentile_cont , PostgreSQL will interpolate the median value as the average of 2 middle values (210,215).

#3

Thanks sir!!!

#4

But it is not working in mysql workbench.

#5

this code is for postgresql and not for mysql workbench

#6

so, how can we do it in mysql?

#7

Try this out once

WITH Numbered AS 
(
SELECT *, COUNT(*) OVER () AS Cnt,
    ROW_NUMBER() OVER (ORDER BY val) AS RowNum
FROM yourtable
)
SELECT id, val
FROM Numbered
WHERE RowNum IN ((Cnt+1)/2, (Cnt+2)/2)
;

You can also do it this way :

#8

Yes!! it is working…Thanks a lot.