PERCENTILE_CONT aggregate function
The PERCENTILE_CONT function returns a percentile of a set of values. The set of values is treated as a continuous distribution. The calculated percentile is an interpolated value that might not have appeared in the input set.
The schema is SYSIBM.
 percentileexpr
 Specifies the percentile that is to be calculated by the function. percentileexpr must return a builtin numeric, character, or graphic string data type that is not a LOB. If the value is not a numeric data type is cast to DECFLOAT(34) before the function is evaluated. The value must be in the range 0–1.
 WITHIN GROUP
 Specifies that the percentile is to be calculated over the rows that are identified in the group.
 sortexpression
 Specifies the set of values over which to calculate the percentile and the order of the set. sortexpression must return a builtin numeric data type. A string value is implicitly cast to a numeric value. sortexpression must not include a scalar fullselect, or any function that is nondeterministic or has an external action. sortkeyexpression must not reference a column for which a column mask is defined.
 ASC
 Specifies that the percentile is calculated using values from sortexpression in ascending order.
 DESC
 Specifies that the percentile is calculated using values from sortexpression in descending order.
The result is the value at the percentile that is specified by percentileexpr over the set of values identified by sortexpression.
For PERCENTILE_CONT, the set of values is treated as a continuous distribution. The calculated percentile is a interpolated value that might not have appeared in the input set. If the argument is DECFLOAT(n), the result of the function is DECFLOAT(34). Otherwise, the result of the function is a doubleprecision floating point number.
The function is applied to the set of values that are derived from sortexpression by the elimination of null values.
The result can be null. If percentileexpr is null, or if the function is applied to an empty set, the result is the null value.
The result of using PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER
BY sortexpression)
is equivalent to
MEDIAN(numericexpression)
.
Examples for PERCENTILE_CONT

This example calculates the median salary of the employees in department D11 from the EMP table.
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY SALARY) FROM DSN8C10.EMP WHERE WORKDEPT = ‘D11’;
The result is 24,680.00. Department D11 has 11 employees. The middle row of a group of 11 values in the sixth row. Because there are an odd number of rows, the PERCENTILE_CONT function for the percentile 0.5 returns the value of the sixth row, which is 24,680.00.

This example calculates the median commission of the employees in department E21 from the EMP table.
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY COMM) FROM DSN8C10.EMP WHERE WORKDEPT = ‘E21’;
The result is 1968.50. This example has an even number of rows, so the PERCENTILE_CONT function is computed by interpolating a value between the two middle rows. The two middle rows are row three with a value of 1907.00 and row four with a value of 2030.00. PERCENTILE_CONT is computed by averaging those two values, which is 1968.50.

This example calculates the 95th percentile of total salary per department from the EMP table.
SELECT PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY SALARY+BONUS) FROM DSN8C10.EMP GROUP BY WORKDEPT;