MySQL Median Value

The middle value of the dataset is called the median value according to the statistics. The median value will be the middle number of a list containing odd number items. The median value will be calculated by finding the average of the two middle numbers of the list if the total items of the list are even. It is necessary to sort the list in ascending order to find out the median value. There is no built-in function in MySQL to find out the median value. The median value is calculated in MySQL when writing the query. The way to find out the median value in MySQL has been shown in this tutorial by using the table.

Prerequisites:

You have to create a table with data in a MySQL database to find the median value of the table data. Open the terminal and connect with the MySQL server by executing the following command:

$ sudo mysql -u root

Run the following command to create a database named test_db:

CREATE DATABASE test_db;

Run the following command to select the database:

USE test_db;

Run the following query to create a table named marks with five fields:

CREATE TABLE marks(
id INT AUTO_INCREMENT PRIMARY KEY,
course_code VARCHAR(10) NOT NULL,
course_name VARCHAR(30),
std_id VARCHAR(20),
obtained_marks INT);

Run the following INSERT query to insert five values into the marks table:

INSERT INTO `marks` (`id`, `course_code`, `course_name`, `std_id`, `obtained_marks`) VALUES
(NULL, 'C-4533', 'Java Programming', '2312', '90'),
(NULL, 'C-7845', 'PHP Programming', '9564', '78'),
(NULL, 'C-3009', 'Web Design', '9076', '65'),
(NULL, 'C-4511', 'Python Programming', '8923', '75'),
(NULL, 'C-7645', 'Bash Programming', '1289', '81');

Run the SELECT query to read the values of the marks table:

SELECT * FROM marks;

The obtained_marks field of the marks table contains the numeric values, and the median value of this column has been searched in the next part of this tutorial:

To find out the median value in MySQL, an index number will require attaching to each row of the table. The following SET command will initialize the @row_index variable with the value -1 because the index of the record will be counted from 0:

SET @row_index := -1;

The records of the table will require to sort based on the values of the obtained_marks field to find out the median value of this column. So, run the following query to sort the table based on the obtained_marks field and attach the index value with each row:

SELECT @row_index:=@row_index + 1 AS row_index, obtained_marks
FROM marks
ORDER BY obtained_marks;

Output:

The following output will appear after executing the previous commands. After sorting, the first index will contain the lowest value of the obtained_marks column, and the last index will have the highest value of the obtained_marks column:

The total number of records in the marks table is 5, which is odd. So, the median value of the obtained_marks column will find out by the following SELECT query. The last value of the row index is 4, according to the output of the previous query. Both FLOOR and CEIL values of the 4/2 are 2. The corresponding obtained_marks value of the row index 2 is 78.

SELECT *
FROM (
SELECT @row_index:=@row_index + 1 AS row_index, obtained_marks
FROM marks ORDER BY obtained_marks ) AS subquery
WHERE subquery.row_index IN (FLOOR(@row_index / 2) , CEIL(@row_index / 2));

Output:

The following output will appear after executing the previous query:

The next part of this tutorial shows finding the median value for an even number of records. Run the following INSERT query to add another record to the marks table to make the total number of records of the table to 6 that is even:

INSERT INTO `marks` (`id`, `course_code`, `course_name`, `std_id`, `obtained_marks`) VALUES (NULL, 'C-1122', 'C++ Programming', '6745', '66');

Run the following SELECT query to read the current values of the marks table:

SELECT * FROM marks;

Output:

The following output will appear after executing the above INSERT and SELECT query:

If the following query is executed again for the table of even number of rows, then two median values will be generated that is not correct:

Output:

The following output will appear after executing the previous query. The result shows two median values here. The average of these values will be the correct median value.

Run the following SELECT query to find out the correct median values of the obtained_marks column of the marks table by using the AVG() aggregate function into the query. Here, two SELECT queries are used. The inner SELECT query is used to find out the median value that can be one or two values based on the total number of rows of the table. The outer SELECT query will find out the average value or values of the obtained_marks column returned by the inner SELECT query. Based on the content of the marks table, the query will generate the average of obtained_marks values of the 3rd and 4th rows of the marks table.

SELECT AVG(subquery.obtained_marks) AS median_value
FROM (
SELECT @row_index:=@row_index + 1 AS row_index, obtained_marks
FROM marks
ORDER BY obtained_marks
) AS subquery
WHERE subquery.row_index
IN (FLOOR(@row_index / 2) , CEIL(@row_index / 2));

Output:

The following output will appear after executing the previous query. The average of 75 and 78 is (75+78)/2 or 76.50:

Conclusion:

The way of calculating the median value of the particular column of the table that may contain even or odd numbers of rows has been shown in this tutorial by using the SELECT query. This tutorial will help the user understand the MySQL median value properly. We hope you found this article helpful. Check out other Linux Hint Articles for more tips and tutorials.

MySQL Median Value

Prerequisites:

Output:

Output:

Output:

Output:

Output:

Conclusion:

About the author

Fahmida Yesmin