CODEWITHSUNDEEP
mysqlsqlmariadbcommon-table-expressionmedian
Tom Rossi
Tom Rossi

Reputation: 12066

Calculating a simple median on a column in MySQL

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

Upvotes: 7

Views: 9248

Answers (3)

GMB
GMB

Reputation: 222402

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

Upvotes: -1

Bill Karwin
Bill Karwin

Reputation: 562240

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.

Upvotes: 5

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520908

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.

Upvotes: -1

Related Questions