Median

from the Artful Common Queries page


Statistically, the median is the middle value--the value that is smaller than that found in half of all remaining rows, and larger than that found in the other half:

Some published SQL Median functions don't scale, they're O(n*n)— and often awkward to use.

Here are two Median functions that are accurate and reasonably fast:

drop table if exists data;
create table data( val decimal(10,2) );
insert into data values(1),(3),(6),(9),(10),(11),(12);

select x.val as Median
from data x, data y
group by x.val
having sum(sign(1-sign(y.val-x.val))) = ( count(*) + count(*)%2 ) / 2 ;

This one is O(log n):

select round( val, 1 ) as Median
from (
  select d.val, @rownum:=@rownum+1 as `row_number`, @total_rows:=@rownum
  from data d, (select @rownum:=0) z 
  where d.val is not null
  order by d.val
) as x
where x.row_number in( floor((@total_rows+1)/2), floor((@total_rows+2)/2) );
+--------+
| Median |
+--------+
|    9.0 |
+--------+

To use either, substitute the desired table name for data and the column name for val.

Return to the Artful Common Queries page