# Calculate median in SQL

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

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).

Thanks sir!!!

But it is not working in mysql workbench.

this code is for postgresql and not for mysql workbench

so, how can we do it in mysql?

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 :

Yes!! it is working…Thanks a lot.