How to Calculate Median in MySQL

Last updated on May 17th, 2020 at 06:37 am

You may need to calculate median of a set of numbers such as sales, etc to find the typical transaction value of a variable. You need to calculate median in MySQL using SQL queries since there is no function for it. Here’s a simple query to calculate median in MySQL.

Table of Contents

How to Calculate Median in MySQL

Here are the steps to calculate median in MySQL. Let’s say you have the following table

CREATE TABLE exams (
id int(11) NOT NULL auto_increment,
dt date,
score int,
PRIMARY KEY (id)
);

insert into exams (dt,score) values ('2019-01-01',70);
insert into exams (dt,score) values ('2019-02-01',77);
insert into exams (dt,score) values ('2019-03-01',71);
insert into exams (dt,score) values ('2019-04-01',70);

insert into exams (dt,score) values ('2019-05-01',89);
insert into exams (dt,score) values ('2019-06-01',87);
insert into exams (dt,score) values ('2019-07-01',88);
insert into exams (dt,score) values ('2019-08-01',89);

mysql> select * from exams;
+------+------------+-------+
| id   |     dt     | score |
+------+------------+-------+
|   1  | 2019-01-01 |   70  |
|   2  | 2019-02-01 |   77  |
|   3  | 2019-03-01 |   71  |
|   4  | 2019-04-01 |   70  |
|   5  | 2019-05-01 |   89  |
|   6  | 2019-06-01 |   87  |
|   7  | 2019-07-01 |   88  |
|   8  | 2019-08-01 |   89  |
+------+------------+-------+

Let’s say you want to find the median score for the table. Median is the value of the middle item in an array of numbers that has been sorted. If the array has even number of items then, median is the average of the middle 2 values.

Also read: How to Create Pivot Table in MySQL

Calculate Median in MySQL

Here’s the SQL query to calculate median for the score column

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

+------------+
| median_val |
+------------+
|    82.00   |
+------------+

Let’s look at the above query in detail. We use a SQL subquery in this case. The inner query assigns @rownum as incremental index and sorts the selected values. At the end of first pass, @total_rows will contain the row count of selected rows. The outer query uses @total_rows to determine the median, irrespective of whether there are odd or even number of values.

Also read: How to Display Row Values as Columns in MySQL

Calculate median in MySQL after applying filter

Let’s say you want to calculate median for only values that are greater than 80 (>80). You can do that by simply adding a where clause to your query above, as shown below (filter condition mentioned in bold)

SELECT AVG(dd.score) as median_val
FROM (
SELECT d.score, @rownum:=@rownum+1 as `row_number`, @total_rows:=@rownum
  FROM exams d, (SELECT @rownum:=0) r
  WHERE d.score is NOT NULL
  AND d.score>80
  ORDER BY d.score
) as dd
WHERE dd.row_number IN ( FLOOR((@total_rows+1)/2), FLOOR((@total_rows+2)/2) );

+------------+
| median_val |
+------------+
|    88.50   |
+------------+

How to Plot Median Line on Graph/Report

Let’s say you want to display median value along side data values on your report. In that case, you can simply do a cross join your median result table with your original data table, as shown below.

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

+------+------------+-------+------------+
| id   |     dt     | score | median_val |
+------+------------+-------+------------+
|   1  | 2019-01-01 |   70  |   82.0000  |
|   2  | 2019-02-01 |   77  |   82.0000  |
|   3  | 2019-03-01 |   71  |   82.0000  |
|   4  | 2019-04-01 |   70  |   82.0000  |
|   5  | 2019-05-01 |   89  |   82.0000  |
|   6  | 2019-06-01 |   87  |   82.0000  |
|   7  | 2019-07-01 |   88  |   82.0000  |
|   8  | 2019-08-01 |   89  |   82.0000  |
+------+------------+-------+------------+

In the above query, we have done a cross join between exams table with the result of median query (labeled as temp)

Here’s an example of above data plotted on a line chart, created using Ubiq.

You can customize the above queries as per your requirement to calculate median in MySQL. FYI, MariaDB provides an out-of-the-box function MEDIAN() to calculate media for a column of values.

If you want to create charts, dashboards & reports from MySQL database, you can try Ubiq. We offer a 14-day free trial.

MySQL DROP VIEW

How to Create Composite Primary Key in MySQL

Enable remote access to MySQL database