MySQL Calculate Median


Here is an optimized MySQL query for calculating the median of some set of values. It has good performance because it does not need to join to the data table more than once.
SELECT avg(t1.val) as median_val FROM (
SELECT @rownum:=@rownum+1 as `row_number`, d.val
  FROM data d,  (SELECT @rownum:=0) r
  WHERE 1
  -- put some where clause here
  ORDER BY d.val
) as t1, 
(
  SELECT count(*) as total_rows
  FROM data d
  WHERE 1
  -- put same where clause here
) as t2
WHERE 1
AND t1.row_number in ( floor((total_rows+1)/2), floor((total_rows+2)/2) );
http://stackoverflow.com/questions/1291152/simple-way-to-calculate-median-with-mysql/7263925

code snippets are licensed under Creative Commons CC-By-SA 3.0 (unless otherwise specified)