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.
We calculate the median of the Distance from the demo table.
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));
- Beginning with the internal subquery – the select assigns @rowindex as an incremental index for each distance that is selected and sorts the distance.
- 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.
- Then, the SELECT clause of the outer query returns the average of those two values as the median value.
- MySQL | Common MySQL Queries
- MySQL | LEAD() and LAG() Function
- PHP | MySQL UPDATE Query
- PHP | MySQL Database Introduction
- PHP | MySQL ( Creating Database )
- PHP | MySQL ( Creating Table )
- PHP | Inserting into MySQL database
- PHP | MySQL Select Query
- PHP | MySQL Delete Query
- PHP | MySQL ORDER BY Clause
- PHP | MySQL WHERE Clause
- MySQL | CREATE USER Statement
- MySQL | Change User Password
- PHP | MySQL LIMIT Clause
- MySQL | DROP USER
- MySQL | RENAME USER
- MySQL | DATABASE() and CURRENT_USER() Functions
- MySQL | BIN() Function
- MySQL | LAST_DAY() Function
- MySQL | Grant / Revoke Privileges
If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to email@example.com. See your article appearing on the GeeksforGeeks main page and help other Geeks.
Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.