Compute a Median or Arbitrary Percentile

Find the median or any percentile value using `PERCENTILE_CONT` in PostgreSQL, MariaDB, and SQL Server, or a window-function workaround in MySQL and SQLite.

5/5 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

Find the median API response time from five samples

The five values sorted are 88, 95, 120, 145, 310. The middle value (position 3 of 5) is 120, making the median 120. PostgreSQL and MariaDB call PERCENTILE_CONT(0.5) as a plain aggregate. SQL Server's version is a window function that outputs a value for every input row — DISTINCT collapses those five identical 120 values into one row. The MySQL and SQLite workaround uses ROW_NUMBER to label rows 1–5 and COUNT(*) OVER () to get the total, then selects the middle row(s) — FLOOR((5+1)/2) and CEIL((5+1)/2) both equal 3 — and averages them. For an even row count, both floor and ceil pick different rows and AVG interpolates between them, matching PERCENTILE_CONT behaviour.

MySQL SQLite

Engine-specific syntax

Source table data

Setup

CREATE TABLE response_times (endpoint VARCHAR(50), ms INT);

INSERT INTO
  response_times
VALUES
  ('GET /api/users', 120),
  ('GET /api/users', 95),
  ('GET /api/users', 310),
  ('GET /api/users', 88),
  ('GET /api/users', 145);

Validated query

SQL

SELECT
  AVG(ms) AS median_ms
FROM
  (
    SELECT
      ms,
      ROW_NUMBER() OVER (
        ORDER BY
          ms
      ) AS rn,
      COUNT(*) OVER () AS cnt
    FROM
      response_times
  ) t
WHERE
  rn IN (FLOOR((cnt + 1) / 2.0), CEIL((cnt + 1) / 2.0));

Expected result

median_ms
120

MariaDB PostgreSQL

Engine-specific syntax

Source table data

Setup

CREATE TABLE response_times (endpoint VARCHAR(50), ms INT);

INSERT INTO
  response_times
VALUES
  ('GET /api/users', 120),
  ('GET /api/users', 95),
  ('GET /api/users', 310),
  ('GET /api/users', 88),
  ('GET /api/users', 145);

Validated query

SQL

SELECT
  PERCENTILE_CONT(0.5) WITHIN GROUP (
    ORDER BY
      ms
  ) AS median_ms
FROM
  response_times;

Expected result

median_ms
120

SQL Server

Engine-specific syntax

Source table data

Setup

CREATE TABLE response_times (endpoint VARCHAR(50), ms INT);

INSERT INTO
  response_times
VALUES
  ('GET /api/users', 120),
  ('GET /api/users', 95),
  ('GET /api/users', 310),
  ('GET /api/users', 88),
  ('GET /api/users', 145);

Validated query

SQL

SELECT DISTINCT
  PERCENTILE_CONT(0.5) WITHIN GROUP (
    ORDER BY
      ms
  ) OVER () AS median_ms
FROM
  response_times;

Expected result

median_ms
120

PostgreSQL and MariaDB use a direct ordered-set aggregate. SQL Server requires OVER() and DISTINCT. MySQL and SQLite use a ROW_NUMBER workaround.

Useful when

Where this command helps.

computing a median response time that is not skewed by outliers the way AVG would be
finding the 90th-percentile salary to define a pay band threshold

Explanation

What the command is doing.

PERCENTILE_CONT(fraction) WITHIN GROUP (ORDER BY col) is an ordered-set aggregate that interpolates the value at the given percentile position. A fraction of 0.5 returns the median. If the exact position falls between two rows, PERCENTILE_CONT interpolates between them, while PERCENTILE_DISC returns the nearest actual row value. PostgreSQL and MariaDB use the aggregate form directly; SQL Server requires the function to appear inside an OVER clause with DISTINCT to collapse the per-row output to a single row. MySQL and SQLite lack these ordered-set aggregates, but the same result can be computed by locating the middle row(s) with ROW_NUMBER and averaging them with AVG.

Compute a Median or Arbitrary Percentile

Find the median API response time from five samples

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.