Schema and sample data
| emp_id | name | salary |
|---|---|---|
| 1 | Ava Torres | 45000.00 |
| 2 | Ben Ncube | 88000.00 |
| 3 | Cara Singh | 58000.00 |
| 4 | Deng Liu | 61000.00 |
| 5 | Emma Rossi | 70000.00 |
| 6 | Farid Khan | 52000.00 |
Show setup SQL (copy to run)
CREATE TABLE Employees (
emp_id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
salary DECIMAL(10,2) NOT NULL
);
INSERT INTO Employees (emp_id, name, salary) VALUES
(1, 'Ava Torres', 45000.00),
(2, 'Ben Ncube', 88000.00),
(3, 'Cara Singh', 58000.00),
(4, 'Deng Liu', 61000.00),
(5, 'Emma Rossi', 70000.00),
(6, 'Farid Khan', 52000.00);From the Employees table, return the median salary across all employees as a single value in a column named median_salary. The median is the middle value when the salaries are sorted: with an odd number of rows it is the exact middle salary, and with an even number of rows it is the average of the two middle salaries. The sample data below has an even count, so your answer must interpolate between the two middle salaries.
Expected result
| median_salary |
|---|
| 59500 |
Show hint
SQL Server has a built in function for this. PERCENTILE_CONT(0.5) computes the continuous 50th percentile, which is the median. It is an ordered set function used with WITHIN GROUP (ORDER BY salary) and an OVER () clause. Because it acts like a window function it repeats the same value on every row, so wrap the query in SELECT DISTINCT to collapse it to one row.
Try to write the query yourself before you open this.
Show solution and explanation
SELECT DISTINCT
PERCENTILE_CONT(0.5)
WITHIN GROUP (ORDER BY salary)
OVER () AS median_salary
FROM Employees;PERCENTILE_CONT(0.5) asks for the value at the 50th percentile of the salaries, which is the definition of the median. The WITHIN GROUP (ORDER BY salary) clause tells it which column to rank, and OVER () with an empty parentheses applies it across the whole table as a single window. Sorted, the six salaries are 45000, 52000, 58000, 61000, 70000 and 88000. The two middle values are 58000 and 61000, so the median is their average, 59500.
The word "continuous" in PERCENTILE_CONT matters. For an even number of rows the 50th percentile lands exactly halfway between the two middle values, so the function interpolates and returns 59500 rather than snapping to one of the stored salaries. (Its sibling PERCENTILE_DISC would instead return an actual value from the data, 58000 here, which is not the true median for an even count.) The function behaves like a window function and stamps the same result on every row, which is why SELECT DISTINCT is used to reduce the six identical rows down to one.
If you need a portable version that runs on databases without PERCENTILE_CONT, number the rows in ascending and descending order with ROW_NUMBER and compare each position against the total COUNT. The median rows are the ones where the ascending rank is within one of the descending rank; averaging their salaries with AVG handles both the odd case (one middle row) and the even case (two middle rows) automatically. That approach is more verbose but works everywhere.