Back

Explore Courses Blog Tutorials Interview Questions
+1 vote
2 views
in SQL by (20.3k points)

What's the simplest (and hopefully not too slow) way to calculate the median with MySQL? I've used AVG(x) for finding the mean, but I'm having a hard time finding a simple way of calculating the median. For now, I'm returning all the rows to PHP, doing a sort, and then picking the middle row, but surely there must be some simple way of doing it in a single MySQL query.

Example data:

id | val

--------

 1    4

 2    7

 3    2

 4    2

 5    9

 6    8

 7    3

Sorting on val gives 2 2 3 4 7 8 9, so the median should be 4, versus SELECT AVG(val) which == 5.

1 Answer

0 votes
by (40.7k points)

In MySQL/MariaDB, you can try this code:

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) );

According to Steve Cohen, after the first pass, @rownum will contain the total number of rows. 

This will be useful to determine the median, so no second pass or join is needed.

Also, dd.row_number IN(...) and AVG(dd.val) is used to produce 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

Note: MariaDB 10.3.3+ contains the MEDIAN function.

Related questions

0 votes
1 answer
0 votes
1 answer
0 votes
1 answer
asked Jan 5, 2021 in SQL by Appu (6.1k points)
0 votes
1 answer
asked Jul 29, 2019 in SQL by Tech4ever (20.3k points)

Browse Categories

...