Median and Percentiles in SQL (PERCENTILE_CONT Guide)

The median, in PostgreSQL and SQL Server

-- PostgreSQL: no OVER() needed, behaves like a normal aggregate
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY order_value) AS median_order_value
FROM orders;

-- SQL Server: OVER() is required even without PARTITION BY
SELECT DISTINCT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY order_value) OVER () AS median_order_value
FROM orders;

In SQL Server, PERCENTILE_CONT is an analytic (window) function — it returns the same computed value on every row rather than collapsing to a single row, which is why DISTINCT is commonly added to reduce the result down to one row when no grouping is otherwise applied.

PERCENTILE_CONT vs PERCENTILE_DISC

-- Dataset: [10, 20, 30, 40] — median falls between 20 and 30

SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY val) AS cont_median FROM t;
-- Result: 25 — interpolated halfway between 20 and 30

SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY val) AS disc_median FROM t;
-- Result: 20 — the nearest actual value present in the data

PERCENTILE_CONT is generally what's meant by "the median" in a statistical sense. PERCENTILE_DISC is useful specifically when the result needs to be a value that genuinely exists in the dataset — an actual order ID or an actual recorded measurement, not a number interpolated between two of them.

Other percentiles: p90, p95, p99

SELECT
  PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY response_time_ms) AS p90,
  PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY response_time_ms) AS p95,
  PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY response_time_ms) AS p99
FROM requests;

The same function handles any percentile, not just the median — p90/p95/p99 response time is the same PERCENTILE_CONT call with a different fraction, common in performance and SLA reporting.

Per-group medians

-- PostgreSQL: GROUP BY works normally alongside the aggregate
SELECT category, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY order_value) AS median_value
FROM orders
GROUP BY category;

-- SQL Server: PARTITION BY inside OVER(), then DISTINCT to collapse duplicates
SELECT DISTINCT category,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY order_value) OVER (PARTITION BY category) AS median_value
FROM orders;

PostgreSQL treats PERCENTILE_CONT as an ordered-set aggregate, so it works with a plain GROUP BY the same way SUM() or AVG() would. SQL Server treats it strictly as an analytic function needing OVER (PARTITION BY ...), which returns the median on every row of that partition rather than collapsing rows — hence the DISTINCT.

The MySQL workaround

MySQL has no built-in percentile function. The standard workaround finds the middle row (or averages the two middle rows for an even count) after numbering the sorted values:

WITH ordered AS (
  SELECT order_value,
         ROW_NUMBER() OVER (ORDER BY order_value) AS rn,
         COUNT(*) OVER () AS total_rows
  FROM orders
)
SELECT AVG(order_value) AS median_order_value
FROM ordered
WHERE rn IN ((total_rows + 1) DIV 2, (total_rows + 2) DIV 2);

For an odd row count, both expressions in the IN() list resolve to the same middle row, so AVG() over one value just returns that value. For an even count, they resolve to the two middle rows, and AVG() produces the interpolated median between them — the same behavior PERCENTILE_CONT(0.5) gives natively elsewhere.

Common mistakes

• Reaching for AVG() when the goal was actually "typical value" — a handful of outliers can pull an average far from what most rows look like; median doesn't move nearly as much.
• Forgetting SQL Server's OVER() requirement for PERCENTILE_CONT even without any partitioning.
• Not adding DISTINCT in SQL Server after an OVER()-based percentile, ending up with the same value repeated on every row instead of one summary row.
• Using PERCENTILE_DISC by habit when an interpolated median (PERCENTILE_CONT) was actually the intended statistic.