CUME_DIST aggregate function

The CUME_DIST function returns the cumulative distribution of a row that is hypothetically inserted into a group of rows.

Read syntax diagramSkip visual syntax diagram CUME_DIST ( ,expression ) WITHIN GROUP ( order-by-clause )
order-by-clause
Read syntax diagramSkip visual syntax diagram ORDER BY ,sort-keyasc-optiondesc-option
asc-option
Read syntax diagramSkip visual syntax diagram ASC NULLS LASTNULLS FIRST
desc-option
Read syntax diagramSkip visual syntax diagram DESC NULLS FIRSTNULLS LAST

The schema is SYSIBM.

expression
An expression that specifies a row that is hypothetically inserted into a group of rows. The expression must return a value that is a built-in data type. The expression must be a constant, a variable, or a cast of a constant or variable (SQLSTATE 428I9).
WITHIN GROUP
Indicates that the aggregation follows the specified ordering within the grouping set.
order-by-clause
ORDER BY
Specifies the order of the rows from the same grouping set that are processed in the aggregation.
sort-key
The sort key can be a column name or a sort-key-expression. If the sort key is a constant, it does not refer to the position of the output column (as in the ordinary ORDER BY clause), but it is a constant, which implies no sort key.
ASC
Uses the values of the sort-key in ascending order.
DESC
Uses the values of the sort-key in descending order.
NULLS FIRST
The ordering considers null values before all non-null values in the sort order.
NULLS LAST
The ordering considers null values after all non-null values in the sort order.

The number of expressions must be the same as the number of sort-key expressions (SQLSTATE 42822). The data type of each expression and the data type of the corresponding sort-key expression must be compatible (SQLSTATE 42822).

The data type of the result is DECFLOAT(34). The actual result is greater than 0.0 and less than or equal to 1.0.

Example

Set the host variable CD to the cumulative distribution of a hypothetical new employee's salary of 47000 within the salaries of the employees in department 'A00'.
   SELECT CUME_DIST(47000) WITHIN GROUP (ORDER BY SALARY) 
      INTO :CD FROM EMPLOYEE WHERE WORKDEPT = 'A00'