![COUNT function [Aggregate]](arrow-left.gif){kind=icon}
![DATALENGTH function [System]](arrow-right.gif){kind=icon}
The CUME_DIST function calculates the relative position of one value among a group of rows. It returns a decimal value between 0 and 1.
CUME_DIST () OVER (window-spec)
window-spec: See the Usage section below.
The following example returns a result set that provides a cumulative distribution of the salaries of employees who live in California:
SELECT DepartmentID, Surname, Salary,CUME_DIST() OVER (PARTITION BY DepartmentIDORDER BY Salary DESC) "Rank"FROM Employees WHERE State IN ('CA');
The returned result set is:
DepartmentID |
Surname |
Salary |
Rank |
|---|---|---|---|
200 |
Savarino |
72300.000 |
0.333333 |
200 |
Clark |
45000.000 |
0.666667 |
200 |
Overbey |
39300.000 |
1.000000 |
Sybase IQ calculates the cumulative distribution of a value of x in a set S of size N using:CUME_DIST(x) = number of values in S coming before and including x in the specified order / N
Composite sort-keys are not currently allowed in the CUME_DIST function. You can use composite sort-keys with any of the other rank functions.
You can specify elements of window-spec either in the function syntax (inline), or with a WINDOW clause in the SELECT statement. The window-spec must contain the ORDER BY clause, and cannot contain a ROWS or RANGE clause. For information on how to specify the window, see “Analytical functions”.
DISTINCT is not supported.
