PERCENTILE_CONT aggregate function

The PERCENTILE_CONT function returns the value that corresponds to the specified percentile given a sort specification by using a continuous distribution model.

Read syntax diagramSkip visual syntax diagram PERCENTILE_CONT ( percentile ) WITHIN GROUP ( ORDER BY sort-key ASCDESC )

The schema is SYSIBM.

percentile
An expression that specifies the percentile. The expression must return a value that is a built-in numeric data type, CHAR, or VARCHAR data type. In a Unicode database, the expression can also be a GRAPHIC or VARGRAPHIC data type. CHAR, VARCHAR, GRAPHIC, and VARGRAPHIC are supported through implicit casting. If the expression is not a numeric data type, it is cast to DECFLOAT(34) before the function is evaluated. The expression must be a constant, a variable, or a cast of a constant or variable (SQLSTATE 428I9). The value must be 0 - 1 (SQLSTATE 22003).
WITHIN GROUP
Indicates that the aggregation follows the specified ordering within the grouping set.
ORDER BY
Specifies the order of the rows from the same grouping set that are processed in the aggregation.
sort-key
The sort key must be a column name or an expression that contains a column reference. sort-key must be a built-in numeric data type (SQLSTATE 42822). If the single column result set specified by sort-key contains one or more NULL values, they are not considered and a warning is returned (SQLSTATE 01003).
ASC
Processes the sort-key in ascending order.
DESC
Processes the sort-key in descending order.

If the data type of sort-key is DECFLOAT(n), the data type of the result is DECFLOAT(34). Otherwise, the data type of the result is DOUBLE.

The result can be null. If percentile is null or the single column result set specified by sort-key is empty, the result is NULL.

When used in an OLAP specification, only the window-partition-clause can be specified.

Example

Set the host variable PC to the value that corresponds to the 75th percentile of the salaries of the employees in department 'E21' using a continuous distribution model.
SELECT PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY SALARY) 
   INTO :PC FROM EMPLOYEE 
   WHERE WORKDEPT = 'E21'
PC is set to a value of 44987.50.
The following result set is shown for reference:
SELECT SALARY FROM EMPLOYEE WHERE WORKDEPT = 'E21' 
   ORDER BY SALARY

SALARY     
-----------
   31840.00
   35370.00
   39950.00
   43840.00
   45370.00
   86150.00

  6 record(s) selected.