WITHIN GROUPFunctions
Buy Me A Coffee

Ordered-set aggregate functions that require a sort order to compute their result: PERCENTILE_CONT (interpolated median), PERCENTILE_DISC (exact value), and MODE (most frequent value).

WITHIN GROUP is the syntax used for ordered-set aggregates (e.g., PERCENTILE_CONT, PERCENTILE_DISC, MODE) that require an ORDER BY clause to specify the ordering of input rows. Supported in PostgreSQL and Oracle as group aggregates, but SQL Server requires OVER() for window function usage.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✗ Not Supported — No PERCENTILE_CONT, PERCENTILE_DISC, or MODE. Median requires a workaround with subqueries or variables.
PostgreSQL ✓ Supported 9.4 PERCENTILE_CONT, PERCENTILE_DISC, and MODE() all supported as true group aggregates (no OVER clause required). PERCENTILE_CONT interpolates; PERCENTILE_DISC returns an actual data value.
SQL Server ✓ Supported 2012 PERCENTILE_CONT and PERCENTILE_DISC supported, but ONLY as window functions — they require an OVER() clause and cannot be used as standalone group aggregates. No MODE() function.
Oracle ✓ Supported 8i PERCENTILE_CONT and PERCENTILE_DISC supported as both group aggregates and analytic functions. No MODE() — use STATS_MODE(expr) instead (added in Oracle 10g).
SQLite ✗ Not Supported — No ordered-set aggregates.

Details

Key gotcha: SQL Server only supports these as window functions (OVER() clause required), not standalone group aggregates. PostgreSQL is the only engine with MODE(). Oracle uses STATS_MODE() as an equivalent.

Standard Syntax

SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median_salary, MODE() WITHIN GROUP (ORDER BY dept_id) AS most_common_dept FROM employees;

Version Support

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

Per-Database Syntax & Notes

MySQL

No PERCENTILE_CONT, PERCENTILE_DISC, or MODE. Median requires a workaround with subqueries or variables.

-- Median workaround (MySQL 8.0+): WITH ranked AS ( SELECT salary, ROW_NUMBER() OVER (ORDER BY salary) AS rn, COUNT(*) OVER () AS total FROM employees ) SELECT AVG(salary) FROM ranked WHERE rn IN (FLOOR((total+1)/2), CEIL((total+1)/2));

PostgreSQL

PERCENTILE_CONT, PERCENTILE_DISC, and MODE() all supported as true group aggregates (no OVER clause required). PERCENTILE_CONT interpolates; PERCENTILE_DISC returns an actual data value.

SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median, PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY salary) AS median_exact, MODE() WITHIN GROUP (ORDER BY dept_id) AS most_common_dept FROM employees;

SQL Server

PERCENTILE_CONT and PERCENTILE_DISC supported, but ONLY as window functions — they require an OVER() clause and cannot be used as standalone group aggregates. No MODE() function.

-- NOTE: requires OVER() in SQL Server: SELECT DISTINCT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) OVER () AS median, PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY salary) OVER () AS median_exact FROM employees;

Oracle

PERCENTILE_CONT and PERCENTILE_DISC supported as both group aggregates and analytic functions. No MODE() — use STATS_MODE(expr) instead (added in Oracle 10g).

SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median FROM employees; SELECT STATS_MODE(dept_id) AS most_common_dept FROM employees;

SQLite

No ordered-set aggregates.

-- Not supported.