SQL Median: 4 Ways to Calculate the Median, With Tested Queries
SQL has AVG built in everywhere, but the SQL median is trickier: some databases have PERCENTILE_CONT or MEDIAN, others have nothing. Learn why the median beats the average for skewed data, the built-in functions by database, a portable window-function method, the LIMIT and OFFSET trick, per-group medians, NULLs and performance, with every query run.
- The SQL median is the middle value of a sorted column; with an even count it is the average of the two middle values.
- One outlier salary of 400 pushed our engineering average to 181.25 while the median stayed at 115.
- PostgreSQL, Oracle and SQL Server use
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY col); MySQL and SQLite need a workaround. - On 200,001 rows, the LIMIT and OFFSET method took 5 ms with an index, versus 220 ms for the window-function method.
Average salary, average order value, average response time: averages are everywhere, and they are easily distorted. One executive salary or one ten-second request drags the mean far from what a typical row looks like. The median, the value in the middle, resists those outliers. Yet many developers are surprised to find there is no universal MEDIAN() in SQL. This guide shows four reliable ways to compute a SQL median, all tested.
We ran every query on SQLite 3.50.4 through Python and checked each result against Python’s statistics.median. Built-in function syntax for other databases comes from their documentation and was not run here.
Median vs average
avg salary (eng): 181.25
median salary (eng): 115.0
Our engineering team has salaries of 95, 110, 120 and one outlier of 400. The average, 181.25, is higher than three of the four people earn. The median, 115, is the average of the two middle values (110 and 120) and describes a typical salary far better. Use the median for skewed data: incomes, prices, response times, session lengths and house prices. Use the average when every unit counts equally toward a total, such as revenue per day.
The sample data
CREATE TABLE sal (dept TEXT, name TEXT, salary INT);
INSERT INTO sal VALUES
('eng','Asha',120), ('eng','Ravi',95), ('eng','Meera',110), ('eng','Kabir',400),
('ops','Lina',60), ('ops','Zoya',70), ('ops','Dev',65), ('ops','Ira',80);
Eight salaries in two departments of four. Even group sizes are deliberate: they force the “average of the two middle values” rule that naive solutions often get wrong.
Method 1: built-in percentile functions
-- PostgreSQL, Oracle, SQL Server (2012+), Snowflake, Db2
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median
FROM sal;
-- Oracle, Snowflake, Redshift, MariaDB 10.3+, DuckDB
SELECT MEDIAN(salary) FROM sal;
PERCENTILE_CONT(0.5) is the standard SQL way: it sorts the values and interpolates the 50th percentile, which for an even count is exactly the average of the two middle values. In SQL Server it is a window function, so add OVER () or OVER (PARTITION BY dept) and DISTINCT. Our SQLite build had neither:
median() -> no such function: median
percentile_cont -> no such function: percentile_cont
Recent SQLite versions ship an optional percentile extension, but it is not compiled into every build, including the one bundled with our Python. MySQL also lacks a median function. For those, use one of the next methods.
Method 2: window functions (portable)
WITH r AS (
SELECT dept, salary,
ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary) AS rn,
COUNT(*) OVER (PARTITION BY dept) AS n
FROM sal
)
SELECT dept, AVG(salary) AS median
FROM r
WHERE rn IN ((n + 1) / 2, (n + 2) / 2)
GROUP BY dept;
-- eng | 115.0
-- ops | 67.5
Number the rows in order, count them, and keep the middle one or two. With integer division, (n + 1) / 2 and (n + 2) / 2 are the same row when n is odd and the two middle rows when it is even, so one AVG handles both cases. Python’s statistics.median gave the identical 115.0 and 67.5. This works in every database with window functions: PostgreSQL, MySQL 8+, MariaDB, SQL Server, Oracle and SQLite 3.25+. See our ROW_NUMBER guide and window functions guide.
Method 3: LIMIT and OFFSET
SELECT AVG(salary) FROM (
SELECT salary FROM sal ORDER BY salary
LIMIT 2 - (SELECT COUNT(*) FROM sal) % 2
OFFSET (SELECT (COUNT(*) - 1) / 2 FROM sal)
);
-- 87.5
Skip to the middle and take one row (odd count) or two rows (even count), then average them. For all eight salaries this returned 87.5, matching Python. It only computes one overall median, not per group, but with an index on the sorted column it is very fast, as the performance section shows. SQLite accepts subqueries in LIMIT and OFFSET as shown; MySQL requires constants there, so compute the count first or use a prepared statement. The same paging ideas appear in our SQL pagination guide.
Method 4: a custom aggregate function
import sqlite3, statistics
class Median:
def __init__(self): self.v = []
def step(self, x):
if x is not None: self.v.append(x)
def finalize(self): return statistics.median(self.v) if self.v else None
con.create_aggregate("my_median", 1, Median)
con.execute("SELECT dept, my_median(salary) FROM sal GROUP BY dept").fetchall()
# [('eng', 115.0), ('ops', 67.5)]
SQLite lets an application register its own aggregate, here written in Python. After registration, my_median works like any built-in aggregate, including with GROUP BY. PostgreSQL extensions and SQL Server CLR functions offer the same idea at the database level.
Median per group
Methods 1, 2 and 4 handle groups directly: PERCENTILE_CONT ... GROUP BY dept in PostgreSQL and Oracle, PARTITION BY dept in the window method, and GROUP BY with a custom aggregate. Per-group medians are where the window method shines in databases without built-ins. Combine them with GROUP BY and HAVING to filter groups, for example departments whose median salary is under a threshold.
Handling NULL values
INSERT INTO sal VALUES ('ops', 'Om', NULL);
-- window method with WHERE salary IS NOT NULL: ops median still 67.5
Built-in percentile functions ignore NULLs, like other aggregates. In the window method you must exclude them yourself with WHERE salary IS NOT NULL inside the CTE; otherwise the NULL is counted in n and sorted to one end, shifting the middle. Our custom aggregate skipped None in step. Decide explicitly whether missing values should be excluded or treated as zero; see SQL NULL values.
Tip: always validate a hand-written median against a trusted tool, as we did with Python, using both an odd and an even group size.
Other percentiles: p90 and beyond
WITH r AS (
SELECT salary, ROW_NUMBER() OVER (ORDER BY salary) AS rn, COUNT(*) OVER () AS n
FROM sal WHERE dept = 'eng'
)
SELECT salary FROM r WHERE rn = CAST(0.9 * n + 0.999999 AS INT);
-- 400
The same ranking approach gives any percentile. This version uses the “nearest rank” definition (round 0.9 times n up to a whole row), which on four rows returns the top value, 400. PERCENTILE_CONT(0.9) would interpolate instead. Percentiles such as p95 and p99 are standard for response times; in practice, monitoring systems compute them for you.
PERCENTILE_CONT vs PERCENTILE_DISC
| Function | Returns | Median of 95, 110, 120, 400 |
|---|---|---|
PERCENTILE_CONT(0.5) |
Interpolated value, may not exist in the data | 115 |
PERCENTILE_DISC(0.5) |
An actual value from the column | 110 |
PERCENTILE_DISC returns the first value whose cumulative position reaches the percentile, so it is always a real data point, useful when the answer must be an existing price or ID. PERCENTILE_CONT matches the statistical median. The 110 shown for DISC follows the standard definition; we did not run it here.
Performance on 200,001 rows
| Method (SQLite, indexed column) | Result | Time |
|---|---|---|
| Window functions | 500765.0 | 220 ms |
| LIMIT and OFFSET | 500765.0 | 5 ms |
| Custom Python aggregate | 500765 | 87 ms |
All three agreed. The LIMIT and OFFSET version walked the index to the middle and read two rows, while the window method numbered every row. A median always needs ordering, so an index on the column helps every method, and for very large tables some databases offer approximate functions such as APPROX_PERCENTILE that trade a little accuracy for speed.
SQL median by database
| Database | Recommended approach |
|---|---|
| PostgreSQL | PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x) |
| Oracle, Snowflake, Redshift | MEDIAN(x) or PERCENTILE_CONT |
| SQL Server | PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x) OVER (...) |
| MySQL 8+ | Window-function method |
| MariaDB 10.3+ | MEDIAN(x) OVER (...) or PERCENTILE_CONT |
| SQLite | Window method, LIMIT/OFFSET, or a registered aggregate |
Final thoughts
The SQL median gives a more honest “typical value” than the average whenever data is skewed. Use PERCENTILE_CONT(0.5) where it exists, the window-function method for portable per-group medians, LIMIT and OFFSET for a fast single median on an indexed column, and a custom aggregate when your database allows it. Handle NULLs deliberately, test with odd and even counts, and round the result for display with SQL ROUND.
Frequently asked questions
How do I calculate the median in SQL?
Use PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY column) in PostgreSQL, Oracle or SQL Server. In MySQL or SQLite, number the rows with ROW_NUMBER and average the middle one or two values.
Does MySQL have a median function?
No. MySQL has no built-in median, so use the window function method with ROW_NUMBER and COUNT, or compute the median in your application.
What is the median when there is an even number of rows?
It is the average of the two middle values. For 95, 110, 120 and 400 the median is the average of 110 and 120, which is 115.
What is the difference between PERCENTILE_CONT and PERCENTILE_DISC?
PERCENTILE_CONT interpolates and can return a value that is not in the data. PERCENTILE_DISC always returns an actual value from the column.
Why use the median instead of the average?
The median is not pulled by extreme values. In our example one high salary raised the average to 181.25 while the median stayed at 115, a far better picture of a typical salary.
Keep learning
Need help with testing, WordPress or SEO?
We help teams ship better software and grow their sites. Tell us what you are working on and we will suggest a clear next step.