PERCENTILE FUNCTIONSFunctions
Buy Me A Coffee

Compute percentile values within a distribution. PERCENTILE_CONT returns an interpolated value; PERCENTILE_DISC returns an actual value from the dataset. Used for medians, quartiles, and statistical analysis.

PostgreSQL aggregate that computes a continuous percentile value (interpolated). PERCENTILE_CONT(0.5) returns the median. Essential for robust statistical measures.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✗ Not Supported — No PERCENTILE_CONT or PERCENTILE_DISC aggregate functions. Common workarounds: use a window function approach with ROW_NUMBER() and COUNT(*), or (MySQL 8.0+) use the window function versions of percentile approximation. NTILE(100) produces percentile buckets, not values.
PostgreSQL ✓ Supported 9.4 Both PERCENTILE_CONT and PERCENTILE_DISC are supported as ordered-set aggregate functions using the WITHIN GROUP (ORDER BY ...) syntax. Also available as window functions via FILTER clause. The array form PERCENTILE_CONT(ARRAY[0.25, 0.5, 0.75]) computes multiple percentiles in a single pass. Works on numeric and interval types.
SQL Server ✓ Supported 2012 SQL Server implements PERCENTILE_CONT and PERCENTILE_DISC as window functions (with OVER clause), not as aggregate functions. This means they always require an OVER clause and are computed per-row — to get a single value per group, use a subquery or DISTINCT on the result. The WITHIN GROUP clause is NOT used in SQL Server's syntax.
Oracle ✓ Supported 8.1.6 Oracle supports both the aggregate form (WITHIN GROUP, no OVER clause) and the analytic/window function form (WITHIN GROUP + OVER clause). The aggregate form returns one row per group; the analytic form computes the percentile and returns it for every row in the partition. Supports numeric and datetime ORDER BY expressions.
SQLite ✗ Not Supported — No PERCENTILE_CONT or PERCENTILE_DISC. Use the ROW_NUMBER window function workaround to compute a median. For grouped medians, a CTE is typically clearest.

Details

PERCENTILE_CONT vs PERCENTILE_DISC: CONT returns an interpolated value (e.g., the average of the two middle values for an even dataset), DISC returns the closest actual value in the dataset. For an even-count dataset, CONT(0.5) returns the average of rows n/2 and n/2+1; DISC(0.5) returns the value at row n/2. The biggest syntax divergence is SQL Server: it implements these as window functions requiring an OVER clause, while the SQL standard and Oracle define an aggregate form using only WITHIN GROUP. MySQL and SQLite have no native support; the ROW_NUMBER workaround is the portable fallback.

Standard Syntax

-- SQL standard (ordered-set aggregate): SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median_salary, PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY salary) AS median_disc FROM employees;

Version Support

MySQL: Not supported PostgreSQL: Since 9.4 SQL Server: Since 2012 Oracle: Since 8.1.6 SQLite: Not supported

Per-Database Syntax & Notes

MySQL

No PERCENTILE_CONT or PERCENTILE_DISC aggregate functions. Common workarounds: use a window function approach with ROW_NUMBER() and COUNT(*), or (MySQL 8.0+) use the window function versions of percentile approximation. NTILE(100) produces percentile buckets, not values.

-- Workaround: median via ROW_NUMBER (MySQL 8.0+): WITH ranked AS ( SELECT salary, ROW_NUMBER() OVER (ORDER BY salary) AS rn, COUNT(*) OVER () AS cnt FROM employees ) SELECT AVG(salary) AS median FROM ranked WHERE rn IN (FLOOR((cnt+1)/2), CEIL((cnt+1)/2)); -- Approximate median via AVG on middle rows: SELECT AVG(salary) AS approx_median FROM ( SELECT salary FROM employees ORDER BY salary LIMIT 2 - (SELECT COUNT(*) FROM employees) MOD 2 OFFSET (SELECT (COUNT(*) - 1) / 2 FROM employees) ) sub;

PostgreSQL

Both PERCENTILE_CONT and PERCENTILE_DISC are supported as ordered-set aggregate functions using the WITHIN GROUP (ORDER BY ...) syntax. Also available as window functions via FILTER clause. The array form PERCENTILE_CONT(ARRAY[0.25, 0.5, 0.75]) computes multiple percentiles in a single pass. Works on numeric and interval types.

-- Median (continuous interpolation): SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median_salary FROM employees; -- Median (actual dataset value): SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY salary) AS median_salary FROM employees; -- Multiple percentiles in one pass: SELECT PERCENTILE_CONT(ARRAY[0.25, 0.5, 0.75]) WITHIN GROUP (ORDER BY salary) AS quartiles FROM employees; -- Per-group: SELECT dept, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median FROM employees GROUP BY dept;

SQL Server

SQL Server implements PERCENTILE_CONT and PERCENTILE_DISC as window functions (with OVER clause), not as aggregate functions. This means they always require an OVER clause and are computed per-row — to get a single value per group, use a subquery or DISTINCT on the result. The WITHIN GROUP clause is NOT used in SQL Server's syntax.

-- SQL Server uses OVER, not aggregate form: SELECT DISTINCT dept, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) OVER (PARTITION BY dept) AS median_salary, PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY salary) OVER (PARTITION BY dept) AS median_disc FROM employees; -- Note: result is repeated per row; DISTINCT collapses them: SELECT dept, p50 FROM ( SELECT dept, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) OVER (PARTITION BY dept) AS p50 FROM employees ) t GROUP BY dept, p50;

Oracle

Oracle supports both the aggregate form (WITHIN GROUP, no OVER clause) and the analytic/window function form (WITHIN GROUP + OVER clause). The aggregate form returns one row per group; the analytic form computes the percentile and returns it for every row in the partition. Supports numeric and datetime ORDER BY expressions.

-- Aggregate form (one result per group): SELECT dept, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median_salary, PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY salary) AS median_disc FROM employees GROUP BY dept; -- Analytic form (result per row): SELECT dept, salary, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) OVER (PARTITION BY dept) AS dept_median FROM employees;

SQLite

No PERCENTILE_CONT or PERCENTILE_DISC. Use the ROW_NUMBER window function workaround to compute a median. For grouped medians, a CTE is typically clearest.

-- Median workaround using ROW_NUMBER (SQLite 3.25+): WITH ranked AS ( SELECT salary, ROW_NUMBER() OVER (ORDER BY salary) AS rn, COUNT(*) OVER () AS cnt FROM employees ) SELECT AVG(CAST(salary AS REAL)) AS median FROM ranked WHERE rn IN ((cnt+1)/2, (cnt+2)/2); -- Per-department median: WITH ranked AS ( SELECT dept, salary, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary) AS rn, COUNT(*) OVER (PARTITION BY dept) AS cnt FROM employees ) SELECT dept, AVG(CAST(salary AS REAL)) AS median FROM ranked WHERE rn IN ((cnt+1)/2, (cnt+2)/2) GROUP BY dept;