PERCENTILE_DISC aggregate function

The PERCENTILE_DISC function returns a percentile of a set of values. Each value in the input set is treated as a discrete value. The calculated percentile is always a value that appeared in the input set.

The schema is SYSIBM.

percentile-expr: Specifies the percentile that is to be calculated by the function. percentile-expr must return a built-in 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.
sort-expression: Specifies the set of values over which to calculate the percentile and the order of the set. sort-expression must return a built-in numeric data type. sort-expression must not include a scalar fullselect, or any function that is non-deterministic or has an external action. sort-key-expression must not reference a column for which a column mask is defined.
ASC: Specifies that the percentile is calculated using values from sort-expression in ascending order.
DESC: Specifies that the percentile is calculated using values from sort-expression in descending order.

The result is the value at the percentile that is specified by percentile-expr over the set of values identified by sort-expression.

For PERCENTILE_DISC, each value in the input set is treated as a discrete value. The calculated percentile is always a value that appeared in the input set. The data type of the result is the same as the data type of the result of sort-expression.

The function is applied to the set of values that are derived from sort-expression by the elimination of null values.

The result can be null. If percentile-expr is null, or if the function is applied to an empty set, the result is the null value.

Examples for PERCENTILE_DISC

This example calculates the median salary as a discrete value of the employees in department D11 from the EMP table.
```
SELECT PERCENTILE_DISC(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_DISC function for the percentile 0.5 returns the value of the sixth row, which is 24,680.00.
This example calculates the median commission as a discrete value of the employees in department E21 from the EMP table.
```
SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY COMM)
FROM DSN8C10.EMP
WHERE WORKDEPT = ‘E21’;
```
The result is 1907.00. This example has six rows, which is an even number of rows. The PERCENTILE_DISC function is computed by returning the value of the first of the two middle rows, which is row three with a value of 1907.00.

This example calculates the 95th percentile of total salary per department from the EMP table.

SELECT PERCENTILE_DISC(0.95) WITHIN GROUP (ORDER BY SALARY+BONUS)
FROM DSN8C10.EMP
GROUP BY WORKDEPT;