How to Find the Median Salary in SQL?

The problem

Given an employees table with a salary column, return the median salary.

Step 1: Create the table

CREATE TABLE employees (
  id     INT PRIMARY KEY,
  name   VARCHAR(50) NOT NULL,
  salary DECIMAL(10,2)
);

Step 2: Insert sample data

INSERT INTO employees (id, name, salary) VALUES
(1, 'Asha',   40000),
(2, 'Ravi',   50000),
(3, 'Meera',  60000),
(4, 'Kiran',  70000),
(5, 'Neha',   80000),
(6, 'Rohan',  90000);

Step 3: Work out the answer by hand

Before writing SQL, it helps to know what we should get. The median depends on whether the number of rows is odd or even.

• Odd count: the median is the single middle value. With five salaries, it is the third one.
• Even count: there is no single middle value, so the median is the average of the two middle values.

Our table has six salaries, which is an even count. Sorted, they are 40000, 50000, 60000, 70000, 80000, and 90000. The two middle values are 60000 and 70000, so the median is (60000 + 70000) / 2 = 65000.

Approach 1: Built-in functions

Some databases give you a ready-made tool.

PostgreSQL

SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median_salary
FROM employees;

Oracle

SELECT MEDIAN(salary) AS median_salary
FROM employees;

SQL Server

SQL Server offers PERCENTILE_CONT only as a window function, so it repeats the answer on every row. DISTINCT collapses those repeats into one.

SELECT DISTINCT
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) OVER () AS median_salary
FROM employees;

What PERCENTILE_CONT means

The median is simply the 50th percentile, so asking for 0.5 finds the value halfway through the sorted list. The _CONT stands for continuous: when the count is even, it interpolates between the two middle values, which gives 65000 here.

Approach 2: The portable window-function version

MySQL (8.0 and later) has no median function, but this query works there and on the other databases too.

SELECT AVG(salary) AS median_salary
FROM (
  SELECT salary,
         ROW_NUMBER() OVER (ORDER BY salary) AS rn,
         COUNT(*) OVER () AS cnt
  FROM employees
  WHERE salary IS NOT NULL
) t
WHERE rn IN (FLOOR((cnt + 1) / 2.0), CEIL((cnt + 1) / 2.0));

SQL Server spells CEIL as CEILING.

How it works, step by step

The inner query numbers the salaries and attaches the total count to each row.

salary	rn	cnt
40000	1	6
50000	2	6
60000	3	6
70000	4	6
80000	5	6
90000	6	6

The outer query then works out which row numbers are "in the middle":

1. For cnt = 6, (cnt + 1) / 2.0 is 3.5. Its floor is 3 and its ceiling is 4, so rows 3 and 4 are selected.
2. Those rows hold 60000 and 70000, and AVG of them is 65000.

The same query with an odd count

Suppose Rohan leaves and only five salaries remain. Now cnt = 5, so (cnt + 1) / 2.0 is exactly 3. The floor and ceiling are both 3, so only row 3 is selected. Its value is 60000, and AVG of a single row is just that value. The one expression handles both odd and even counts without any special case.

The result

median_salary
65000.00

Compare that with the average, which is 65000 as well here because the data is evenly spread. Add one salary of 900000 and the average jumps to roughly 184,286 while the median moves only to 70000. That is the whole reason to use the median.

Pitfalls to watch for

NULL salaries

PERCENTILE_CONT and MEDIAN ignore NULL values automatically. In the window-function version, the WHERE salary IS NOT NULL inside the subquery does the same job. Without it, NULLs would be counted in cnt and shift the middle position, giving a wrong answer.

CONT versus DISC

PERCENTILE_CONT(0.5) interpolates, so for an even count it can return a value that no employee actually earns. If you want a real salary from the table, use PERCENTILE_DISC(0.5) instead. For our data it returns 60000, the lower of the two middle values.

Using AVG by mistake

It is easy to write AVG(salary) and call it "typical." Keep in mind that the average is sensitive to outliers, while the median is not.

Bonus: median salary per department

Often you want the median within each group. With the portable version, partition both window functions and group the outer query.

SELECT department, AVG(salary) AS median_salary
FROM (
  SELECT department, salary,
         ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary) AS rn,
         COUNT(*) OVER (PARTITION BY department) AS cnt
  FROM employees
  WHERE salary IS NOT NULL
) t
WHERE rn IN (FLOOR((cnt + 1) / 2.0), CEIL((cnt + 1) / 2.0))
GROUP BY department;

This assumes your table has a department column. In PostgreSQL, you can instead add the column to the select list and GROUP BY department alongside PERCENTILE_CONT. In SQL Server, use OVER (PARTITION BY department) with the percentile function.

Which approach should you choose?

Database	Recommended approach
PostgreSQL	PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary)
Oracle	MEDIAN(salary)
SQL Server	PERCENTILE_CONT(0.5) as a window function with DISTINCT
MySQL 8+	The ROW_NUMBER version
Any database, one portable query	The ROW_NUMBER version

Final thoughts

The median is a small idea with a surprisingly rich SQL story: sort the values, find the middle, and average the two middle rows when the count is even. Use the built-in function when your database has one, fall back on the ROW_NUMBER pattern when it does not, and always decide up front how NULL values should be treated. Do that, and you will report pay figures that reflect what a typical employee really earns.