PERCENTILE FUNCTIONS SQL Compatibility | MySQL, PostgreSQL, SQL Server, Oracle, SQLite

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

SQL PERCENTILE FUNCTIONS Compatibility Across Databases
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	Native	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	Native	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	Native	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: Native since 9.4 SQL Server: Native since 2012 Oracle: Native since 8.1.6 SQLite: Not supported

Per-Database Syntax & Notes

MySQL Alternative syntax

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 Native syntax

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 Native syntax

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 Native syntax

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 Alternative syntax

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;