SQL Median: How to Calculate Median in SQL With Practical Examples

Median in SQL calculating depends entirely on your database system, there's no universal MEDIAN () function in standard SQL. Some platforms, like Oracle and Snowflake, have done the work for you - MEDIAN () is built right in. But if you're working in SQL Server, PostgreSQL, or MySQL, you'll need to roll up your sleeves a bit. That means leaning on window functions or writing out the logic yourself to locate that middle value.

In this blog, you’ll learn what SQL median is, why it matters, and how to calculate the SQL median using methods. We will talk about databases such as PostgreSQL, MySQL, SQL Server, and Oracle. Explain when you can use a SQL median function and give you examples that you can use in your projects.

Write SQL that analysts trust. Learn median calculations, window functions, and more with upGrad's expert-led Data Science Courses.

What Is SQL Median and Why Is It Important? 

The median in SQL is the number when values are arranged in order. The median is not affected much by high or very low numbers. This makes the median a good way to find the middle of real-world data sets unlike the average.

For example, consider employee salaries:

Employee	Salary
A	30,000
B	35,000
C	38,000
D	42,000
E	300,000

The average salary is really affected by the salary. The median salary is 38,000, which's a better idea of what most employees actually earn.

often like to use the SQL median when they are working with business data or healthcare data or finance data or customer data.

Also Read: Measure of Central Tendency: Mean, Median, and Mode

Why use the SQL median?

The median is a way to understand a set of data than the average. This is because the median does not take into account the extreme outliers in a dataset. So, the median gives us an idea of what the data is like. 

The SQL median helps answer questions such as:

• What is the typical customer spending amount?
• What is the middle salary in a department?
• What is the median delivery time for orders?
• What is the typical exam score in a class?
• What is the middle house price in a city?

Median vs Average

Feature	Median	Average
Represents	Middle value	Sum divided by count
Sensitive to outliers	No	Yes
Best for skewed data	Yes	No
Built-in SQL support	Depends on database	Yes

Suppose a retail store records these daily sales:

120 
130 
135 
140 
145 
150 
5000 
The average becomes much higher because of the single exceptional sale. The SQL median remains 140, giving a more realistic picture of a normal day.

Also Read: SQL Tutorials : Complete Learning Guide

Is there a SQL median function? 

This is where SQL becomes interesting. Not every database includes a dedicated SQL median function. The support we give depends on the kind of database engine that the user is working with.

Database	Built-in Median Support
PostgreSQL	Yes (PERCENTILE_CONT)
Oracle	Yes (MEDIAN () and PERCENTILE_CONT)
SQL Server	Uses PERCENTILE_CONT ()
MySQL 8+	No direct median function

MySQL does not have a built-in function to calculate the median. You can still find the median of a list of numbers. You can use functions like ROW_NUMBER to do this. You can use LIMIT and OFFSET together.

This can be confusing for people who're new to MySQL. This is because you can use functions like SUM and COUNT, and AVG in databases. If you want to calculate the median in SQL, you have to do it in a way that is specific to the database you are using, such as MySQL.

When should you calculate the SQL median?

For example, a data analyst who is looking at home prices can use the SQL median instead of the average. This is because home prices can be affected by luxury properties. 

You should consider using the SQL median when:

• Your dataset contains unusually high or low values
• You want a typical value instead of an arithmetic average
• You're creating business intelligence dashboards
• You're performing statistical analysis
• You're cleaning or validating data before machine learning

Example dataset

Assume you have the following table:

CREATE TABLE Employees ( 
   EmployeeID INT, 
   EmployeeName VARCHAR(50), 
   Salary INT 
); 

Sample data:

EmployeeID	EmployeeName	Salary
1	Alice	35000
2	Bob	42000
3	Chris	47000
4	Diana	51000
5	Ethan	62000

After sorting the salaries, I found that the middle value is 47,000. This value is what we call the SQL median.

When we have a lot of data, it is hard to find the value by hand. That's why we use SQL queries and a special SQL median function if it is available to help us analyze the data accurately and quickly.

Also Read: SQL Server Architecture: Core Components, Data Management, and Practical Uses

How to Find SQL Median in Different Databases 

There is no SQL query that works for all databases to find the median. Some databases have a built-in function, but others need special tricks.

The good news is that all modern SQL databases can help you find the median. You just need to use the method for your database.

1. SQL Median in PostgreSQL

PostgreSQL does not have a MEDIAN function. PostgreSQL supports PERCENTILE_CONT, which is the way to calculate the median of the PostgreSQL data.

Example:

SELECT 
   PERCENTILE_CONT(0.5) 
   WITHIN GROUP (ORDER BY Salary) AS MedianSalary 
FROM Employees; 

How it works:

• 0.5 represents the 50th percentile.
• WITHIN GROUP sorts the values.
• The function returns the middle value.
• If there is an even number of rows, it interpolates between the two middle values.

This approach is simple, accurate, and performs well on large datasets.

2. SQL Median in SQL Server

SQL Server also supports PERCENTILE_CONT () as an analytic function.

SELECT DISTINCT 
   PERCENTILE_CONT (0.5) 
   WITHIN GROUP (ORDER BY Salary) 
   OVER () AS MedianSalary 
FROM Employees;

Notice the use of the OVER () clause. This is different from PostgreSQL because SQL Server sees PERCENTILE_CONT () as a window function.

This query gives us the median salary for the entire table.

3. SQL Median in Oracle

Oracle provides two common options. The first is the dedicated MEDIAN () aggregate function.

SELECT MEDIAN(Salary) AS MedianSalary 
FROM Employees; 

Oracle also supports PERCENTILE_CONT() if you need more flexibility.

SELECT 
   PERCENTILE_CONT(0.5) 
   WITHIN GROUP (ORDER BY Salary) AS MedianSalary 
FROM Employees; 

When we are working with numbers, MEDIAN is a choice because it is easy to understand. On the other hand, PERCENTILE_CONT is useful when we want to calculate other percentiles at the same time, as the MEDIAN.

4. SQL Median in MySQL

MySQL does not have a built-in SQL function to find the median. To find the median, you can use window functions that were added to MySQL version 8.0.

WITH RankedData AS ( 
   SELECT 
       Salary, 
       ROW_NUMBER() OVER (ORDER BY Salary) AS RowNum, 
       COUNT(*) OVER () AS TotalRows 
   FROM Employees 
) 
SELECT AVG(Salary) AS MedianSalary 
FROM RankedData 
WHERE RowNum IN ( 
   FLOOR((TotalRows + 1) / 2), 
   FLOOR((TotalRows + 2) / 2) 
); 

This solution works for both odd and even numbers of rows.

The query:

• Sorts the salaries
• Assigns a row number
• Counts the total records
• Selects the middle row or two middle rows
• Uses AVG () so both cases return the correct median

This query may look a bit longer than the ones that are already built in. The thing is it is really widely used in a lot of MySQL projects. People use this MySQL query all the time.

Also Read: Components of SQL: What They Are and How They Work

Comparison of SQL Median Methods

Database	Recommended Method	Built-in Support	Difficulty
PostgreSQL	PERCENTILE_CONT()	Yes	Easy
SQL Server	PERCENTILE_CONT()	Yes	Easy
Oracle	MEDIAN()	Yes	Very Easy
Oracle	PERCENTILE_CONT()	Yes	Easy
MySQL 8+	Window Functions	No	Moderate

Odd vs Even Number of Records

The calculation of the number's changes depending on how many rows are available.

Consider this dataset:

Salary

30,000

35,000

42,000

50,000

55,000

There are five values, so the middle value is 42,000.

Now consider another dataset.

Salary

30,000

35,000

42,000

50,000

55,000

60,000

There are six values. The two middle values are 42,000 and 50,000.

The median becomes:

(42,000 + 50,000) ÷ 2 = 46,000 

Functions, like PERCENTILE_CONT () do this automatically. When you write your query, ensure it handles both cases properly.

Common Mistakes When Calculating SQL Median 

A small mistake in your query can give you information especially when you are working with big business data. It is good to check your logic to make sure your results make sense and are correct.

The SQL median can be tricky. Here are a few mistakes that people make when they are working with the SQL median that you should try to avoid:

• Assuming every database supports a MEDIAN() function.
• Forgetting to sort values before identifying the middle record.
• Ignoring the even-number case.
• Using AVG () instead of the median for skewed datasets.
• Applying the median calculation without filtering unwanted or NULL values.

Also Read: Top 20 Essential SQL Tools for Data Analysts and Engineers

Best Practice

When the database you are using has a SQL median function or PERCENTILE_CONT () it is a good idea to use this function. This is because built-in functions are simple to understand. They also have errors. Usually, the database engine can optimize them better.

If the database you are using does not have support, you should use window functions. Do not use the methods that have nested subqueries. Window functions are easy to read and understand. They are also easy to maintain. On SQL systems, they usually work better. The database you are using will be able to handle window functions in an efficient way.

Conclusion

Understanding the SQL median helps you analyze data more accurately, especially when outliers can distort averages. While some databases provide a built-in SQL median function, others require window functions or custom queries. By choosing the right approach for your database, you can calculate reliable median values and make better data-driven decisions. Either way, knowing which approach fits your setup means you can trust your results and make decisions that are grounded in what the data is actually telling you.

Want to explore more about management accounting? Book your free 1:1 personal consultation with our expert today.