Revision history [back]

One way to extract multiple Nth percentiles in MySQL 5.6 is to use the PERCENTILE_DISC() function in combination with a subquery that generates a list of percentile values to extract.

For example, the following query extracts the 20th, 50th, and 90th percentiles for the "salary" column in the "employees" table:

SELECT 
  PERCENTILE_DISC(0.2) WITHIN GROUP (ORDER BY salary) as p20,
  PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY salary) as p50,
  PERCENTILE_DISC(0.9) WITHIN GROUP (ORDER BY salary) as p90
FROM 
  employees;

This query uses the PERCENTILE_DISC() function to calculate the specified percentiles (in this case, the 20th, 50th, and 90th). The WITHIN GROUP (ORDER BY) clause specifies that the percentile values should be calculated based on the ordering of the "salary" column.

To extract multiple percentiles, simply add additional PERCENTILE_DISC() expressions to the SELECT list with the desired percentile values. The subquery inside the function will generate the appropriate percentile values for each expression.