Calculating a simple median on a column in MySQL

Question

I'm struggling to find the solution for a simple median problem. Given a table my_table with just one column:

my_column | 
----------|
10        |
20        |
30        |
40        |
50        |
60        |

How can I call a function to return the median of 35?

I can't figure out how to make this syntax work when all I want is to return the median value:

SELECT
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY my_column) OVER ( PARTITION BY my_column)
FROM
  my_table

@BillKarwin Not true at all. The above data set does not have a single median value, as it has an _even_ number of values. As a result, typically statisticians will report the _mean_ of 30 and 40 as the median. In this case, it is 35, which coincidentally also happens to be the mean. — Tim Biegeleisen, Nov 09 '20 at 16:51
Sorry, I should have used better sample data to keep it more clear! — Tom Rossi, Nov 09 '20 at 16:59
Since you tagged it with mariadb: MariaDB 10.2 has "windowing functions" similar to MySQL 8. — Rick James, Nov 10 '20 at 02:43

Tim Biegeleisen · Answer 1 · 2020-11-09T16:50:04.040

0

You may try:

SELECT col_median
FROM
(
    SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY my_column) OVER () AS col_median
    FROM my_table
) t
LIMIT 1;

Demo

Notes: The PERCENTILE_CONT function is a window function, whose output, in this case, can only be determined after the entire column has been scanned. As a result, the output from the subquery above is actually your column, along with a new column, having the same median value in all rows. But, since you just want to report the median as a single number, I use a LIMIT 1 outer query to obtain that single median value.

edited Nov 09 '20 at 16:50

answered Nov 09 '20 at 16:45

Tim Biegeleisen

387,723
20
200
263

Perfect! If I wanted to get the 0.25, 0.50, 0.75, 0.90 percentiles as well would I just follow that same pattern? – Tom Rossi Nov 09 '20 at 16:58
For the entire table, yes, that should work (play around with my demo link to try it out). – Tim Biegeleisen Nov 09 '20 at 17:01

score 0 · Answer 2 · answered Nov 09 '20 at 17:21

Here's a solution that I tested in MySQL 8.0:

with ranked as (
  select my_column, 
    row_number() over (order by my_column) as r,
    count(my_column) over () as c 
  from my_table
),
median as (
  select my_column 
  from ranked 
  where r in (floor((c+1)/2), ceil((c+1)/2))
)
select avg(my_column) from median

Output:

+----------------+
| avg(my_column) |
+----------------+
|        35.0000 |
+----------------+

I borrowed the method from https://stackoverflow.com/a/7263925/20860 but adapted it to MySQL 8.0 CTE and window functions.

score 0 · Accepted Answer · answered Nov 09 '20 at 17:33

0

I would just use distinct, with an empty OVER() clause:

SELECT DISTINCT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY my_column) OVER () median
FROM my_table

answered Nov 09 '20 at 17:33

GMB

188,822
23
52
100

Calculating a simple median on a column in MySQL

3 Answers3

Demo