1. Home
  2. Blog
  3. Databases & SQL

Databases & SQL

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.

9 min read
SQL median: 4 ways to calculate the median, with tested queries

Key takeaways

  • 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

Work with Stack Quora

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.

Keep reading

More guides on testing, AI & modern development

Enjoyed this article? Pick a topic and jump straight into our newest tutorials.

Artificial Intelligence

Random Forest in Python: Bagging, Feature Sampling and OOB From Scratch

Random forest in Python from scratch: bootstrap sampling, feature sampling, number of trees, out-of-bag evaluation and impurity vs permutation importance.

Read article →
Software Development

MIME Types Explained: Content-Type, nosniff and a Common Types List

MIME types explained and tested in a browser: Content-Type, nosniff, scripts and stylesheets with wrong types, a common MIME types…

Read article →
DevOps

Git Clean Explained: Remove Untracked Files Without Regret

Git clean explained with dry runs: -n, -f, -d, -x, -X, exclusions, paths, nested repositories and interactive mode, plus a…

Read article →
Test Automation

Pytest approx: Comparing Floats With rel, abs and Collections

Pytest approx explained: default tolerances, the near-zero trap, rel and abs, lists and dicts, NaN, NumPy, and how it differs…

Read article →
Software Testing

Accessibility Testing: WCAG Checklist, Automated Tools and Manual Checks

Accessibility testing explained: WCAG basics, an axe-core scan of a broken and a fixed page, what automation missed, contrast checks…

Read article →
Databases & SQL

SQL UNPIVOT: Turn Columns Into Rows, 4 Ways Tested

SQL UNPIVOT explained: turn columns into rows with UNION ALL, CROSS JOIN with CASE, JSON functions or native UNPIVOT, with…

Read article →
Software Development

Intl NumberFormat in JavaScript: Currency, Compact, Percent and Units

Intl.NumberFormat in JavaScript: locales, currency, compact notation, percent, units, rounding, formatToParts, the non-breaking space trap and formatter reuse.

Read article →
Software Development

Python Abstract Class: ABC, abstractmethod and When to Use Them

Python abstract class explained: ABC and abstractmethod, abstract properties, shared logic with super, register, collections.abc and ABC vs Protocol, tested.

Read article →
Software Development

Python Subprocess Explained: run, Capture Output, Timeouts and Safety

Python subprocess explained with tested examples: run, capture_output, check, timeouts, input, environment, shell injection, encoding on Windows and Popen streaming.

Read article →
Databases & SQL

SQL EXPLAIN Plan: How to Read Query Plans and Fix Slow Queries

SQL EXPLAIN plan explained: read scans, index searches, covering indexes and temporary sorts, with before and after timings for five…

Read article →
Artificial Intelligence

Decision Tree in Python: Gini, Entropy and Overfitting From Scratch

Decision tree in Python from scratch: Gini impurity, entropy, information gain, best splits, readable rules, and how depth and leaf…

Read article →
Software Development

URL Query Parameters: Encoding, URLSearchParams and Common Traps

URL query parameters explained: reading and building them with URLSearchParams and urllib, percent encoding, plus signs, repeated keys and nine…

Read article →
DevOps

Git Blame Explained: Find Who Changed a Line, and Why

Git blame explained with a real repository: ignore whitespace, line ranges and functions, ignore-revs files for formatting commits, moved code…

Read article →
Test Automation

Pytest Benchmark: Measure Speed and Catch Performance Regressions

Pytest benchmark tutorial: the benchmark fixture, reading results, parametrized and pedantic benchmarks, saving baselines and failing the build on regressions.

Read article →
Software Testing

Defect Density: Formula, Worked Examples and How to Use It

Defect density explained: the formula, a five-module worked example, per KLOC vs per function point, severity weighting, defect removal efficiency…

Read article →
Databases & SQL

Case Insensitive Search in SQL: LOWER, LIKE, COLLATE and Indexes

Case insensitive search in SQL: LOWER, LIKE, COLLATE NOCASE and expression indexes compared with query plans, plus accented letters and…

Read article →
Databases & SQL

ON DELETE CASCADE in SQL: CASCADE vs SET NULL vs RESTRICT

ON DELETE CASCADE in SQL tested: CASCADE vs SET NULL vs RESTRICT, multi-level cascades, the SQLite foreign key pragma, row…

Read article →
Software Development

JavaScript BigInt Explained: Big Numbers, Division and JSON

JavaScript BigInt explained: the 2^53 limit, creating and converting BigInt, division, mixing types, JSON stringify and parse without losing digits,…

Read article →
Artificial Intelligence

Logistic Regression in Python: Sigmoid, Log Loss and Gradient Descent From Scratch

Logistic regression in Python from scratch: sigmoid, log loss, gradient descent, odds ratios, thresholds, sigmoid overflow and separable data, tested…

Read article →
DevOps

Git Worktree Explained: Work on Two Branches at Once

Git worktree explained: add, list, remove, prune, lock and detached worktrees, a hotfix workflow, and the errors you will hit,…

Read article →
Test Automation

Pytest ini Explained: addopts, markers, testpaths and Precedence

Pytest ini explained: testpaths, addopts, strict markers, xfail_strict, filterwarnings, minversion, config file precedence and command-line overrides, tested.

Read article →
Software Testing

Test Estimation Techniques: WBS, Three Point PERT and Monte Carlo

Test estimation techniques worked through: WBS, three point PERT, total uncertainty, a Monte Carlo check, percentage rules and Wideband Delphi.

Read article →
Databases & SQL

Gaps and Islands in SQL: Find Streaks and Missing Ranges

Gaps and islands in SQL: find login streaks and missing ranges with ROW_NUMBER, DENSE_RANK, LAG and LEAD, plus the duplicate…

Read article →
Databases & SQL

INSERT INTO SELECT in SQL: Copy Rows Between Tables Safely

INSERT INTO SELECT in SQL: copy rows between tables with chosen columns, joins and summaries, avoid duplicates on reruns, upsert,…

Read article →
Artificial Intelligence

Linear Regression in Python: Least Squares From Scratch, With R Squared

Linear regression in Python from scratch: closed form, NumPy least squares and gradient descent, R squared, RMSE, outliers and curved…

Read article →
DevOps

Git Remote Explained: Add, Set-URL, Rename, Remove and Push URLs

Git remote explained: add, set-url, separate push URLs, rename, remove and prune, plus how to avoid leaking tokens in remote…

Read article →
Test Automation

Pytest Mock: mocker.patch, spy, side_effect and autospec Explained

Pytest mock explained: mocker.patch, return_value, side_effect, spy, autospec and PropertyMock, with tested examples for APIs, clocks and retries.

Read article →
Software Testing

System Testing Explained: Types, Process and a Real End-to-End Example

System testing explained: types, entry and exit criteria, the process, and a real end-to-end test suite run against a small…

Read article →
Artificial Intelligence

Naive Bayes Classifier Explained: Build a Spam Filter in Python

Naive Bayes classifier explained by building a spam filter in Python: Bayes rule, the naive assumption, Laplace smoothing, log probabilities…

Read article →
DevOps

Git Branch Explained: Create, Switch, Rename, Delete and Recover

Git branch explained with tested commands: list, create, switch, rename, delete, merged checks, recovering deleted branches, remote tracking and naming.

Read article →
Test Automation

Pytest raises: Testing Exceptions, match and excinfo

Pytest raises explained with tested examples: match regexes, excinfo, custom and chained exceptions, parametrized error cases, pytest.warns and common mistakes.

Read article →
Software Testing

Non Functional Testing: Types, Metrics and a Real Load Test

Non functional testing explained: 10 key types, measurable requirements, latency percentiles and what a small real load test revealed about…

Read article →
Artificial Intelligence

K Means Clustering Explained: How It Works, With Python Code

K means clustering explained: the algorithm, a from-scratch NumPy version, choosing k with the elbow and silhouette methods, k-means++, scaling…

Read article →
DevOps

Git Log Explained: 15 Useful Commands and Pretty Formats

Git log explained with 15 tested commands: oneline, graph, filters by author, date, message and code, file history, stats, pretty…

Read article →
Test Automation

Pytest conftest.py Explained: Shared Fixtures, Hooks and Overrides

Pytest conftest.py explained with a tested suite: shared fixtures, scope and teardown, folder overrides, autouse, custom options, markers and useful…

Read article →
Software Testing

Usability Testing: Methods, Metrics, SUS Score and Checklist

Usability testing explained: methods, planning, writing tasks, running sessions, metrics, SUS scoring, how many users you need and a practical…

Read article →