Calculate Median in MySQL

Introduction

The median is one of the most significant statistics used to present the notion of the middlemost value of a definite set of data if the observed values are arranged in an ascending or a descending order. While in contrast the median is not affected by extreme values that are present within the data set, making the measure extremely useful under skewed distributions. As we know, MySQL does not have an inbuilt support for Median and therefore the process of calculating Median using SQL involves using of several SQL expressions. This article explores the technique of computing for the median value using MySQL.

Importance of the Median

Both the mean and the median are important tools when analyzing the location of data and are used to differentiate between datasets with extreme values and larger ones without these values. For example, in the cases of representing salary levels or house prices, the median is superior as it reflects the more representative average value.

Calculating Median:

SQL Code

 
CREATE TABLE scores (
    id INT AUTO_INCREMENT PRIMARY KEY,
    score INT
);

INSERT INTO scores (score) VALUES
(10), (20), (30), (40), (50), (60), (70), (80), (90), (100);

WITH ranked_scores AS (
    SELECT
        score,
        ROW_NUMBER() OVER (ORDER BY score) AS row_num,
        COUNT(*) OVER () AS total_rows
    FROM scores
)
SELECT AVG(score) AS median_score
FROM ranked_scores
WHERE row_num IN (FLOOR((total_rows + 1) / 2), CEIL((total_rows + 1) / 2));   

Output:

Explanation:

Common Table Expression (CTE):

The third statement states that it defines a named ranked_score within the CTE of the WITH clause.

Inside the CTE:
The ROW_NUMBER() serves the purpose of ranking of record from the result-set in the prescriptive, in given context for SORTED BY parameter it first sorts by score.
Some are COUNT(*) OVER (), which returns the total count, or record size of the referred dataset.
Selecting the Median Value:
The first is fairly simple; the median equals to the sum of values which occupy rank numbers computed as FLOOR((number of records + 1)/2) and CEIL((number of records + 1)/2) and divided by 2 with fractional part rounded to the integer.
These positions are built to the total count of rows they read - including even ones and odd ones - which provides an approximation of the median value of the set.

Conclusion

To compute the median in SQL, especially in MySQL, is very important for business intelligence because it allows evaluating the degree of dispersion of the dataset, free from extreme values. Although, MySQL does not provide in-built median function and therefore the same task can be performed with certain complicated SQL operations like subqueries and window functions.

Next Topic#

← prev next →