Simple way to calculate median with MySQL

Question

What is the simplest (hopefully not too slow) way to calculate the median using MySQL? I'm using AVG(x) to find the mean, but I'm having trouble finding an easy way to calculate the median. Right now, I'm returning all the rows to PHP, sorting, and then selecting the middle rows, but there must be some easy way to do this in a single MySQL query.

Example data:

id | val
--------
 1 4
 2 7
 3 2
 4 2
 5 9
 6 8
 7 3

Sort val gives 2 2 3 4 7 8 9, so the median should be 4, And SELECT AVG(val) == 5.

Answer 1

I just found another answer online in the comments:

Make sure your columns are well indexed and the indexes are used for filtering and sorting. Validate with explain plan.

select count(*) from table --find the number of rows

Calculate the "median" row number. Maybe use: median_row = Floor(count / 2).

Then select it from the list:

select val from table order by val asc limit median_row,1

This should return a row with the values ​​you want.

Answer 2

In MariaDB/MySQL:

SELECT AVG(dd.val) as median_val
FROM (
SELECT d.val, @rownum:=@rownum+1 as `row_number`, @total_rows:=@rownum
  FROM data d, (SELECT @rownum:=0) r
  WHERE d.val is NOT NULL
  -- put some where clause here
  ORDER BY d.val
) as dd
WHERE dd.row_number IN ( FLOOR((@total_rows+1)/2), FLOOR((@total_rows+2)/2) );

Steve Cohen pointed out that after the first pass, @rownum will contain the total number of rows. This can be used to determine the median, so no second pass or concatenation is required.

Additionally, AVG(dd.val) and dd.row_number IN(...) are used to correctly generate the median when there is an even number of records. reasoning:

SELECT FLOOR((3+1)/2),FLOOR((3+2)/2); -- when total_rows is 3, avg rows 2 and 2
SELECT FLOOR((4+1)/2),FLOOR((4+2)/2); -- when total_rows is 4, avg rows 2 and 3

Finally, MariaDB 10.3.3 includes MEDIAN functions