Skip to content
Related Articles

Related Articles

Improve Article
Save Article
Like Article

Calculate Median in MySQL

  • Difficulty Level : Expert
  • Last Updated : 14 Jul, 2020

Median :
In statistics and probability theory, Median is a value separating the higher half from the lower half of a data sample, a population, or a probability distribution. In lay-man language, Median is the middle value of a sorted listed of values.

Calculate Median value in MySQL
Consider a table demo where Name is the student name and Distance is the total distance (in km) from their home to college.

Attention reader! Don’t stop learning now. Learn SQL for interviews using SQL Course  by GeeksforGeeks.

NameDistance
Sumit25
Jaskirat35
Sukrut20
Shivam20
Sanchit45
Prince35
Keshav15
Preet25
Saurav20



We calculate the median of the Distance from the demo table.
Query –

SET @rowindex := -1;
 
SELECT
   AVG(d.distance) as Median 
FROM
   (SELECT @rowindex:=@rowindex + 1 AS rowindex,
           demo.distance AS distance
    FROM demo
    ORDER BY demo.distance) AS d
WHERE
d.rowindex IN (FLOOR(@rowindex / 2), CEIL(@rowindex / 2));

Explanation :

  1. Beginning with the internal subquery – the select assigns @rowindex as an incremental index for each distance that is selected and sorts the distance.
  2. Once we have the sorted list of distances, the outer query will fetch the middle items in the array. If the array contains an odd number of items, both values will be the single middle value.
  3. Then, the SELECT clause of the outer query returns the average of those two values as the median value.

Output –

25.0000
My Personal Notes arrow_drop_up
Recommended Articles
Page :

Start Your Coding Journey Now!