CUME_DIST as Aggregate Function in Oracle SQL – PLSQL

The CUME_DIST function in Oracle SQL / PLSQL is used to get the cumulative distribution of a value in a group of values.

CUME_DIST function in Oracle SQL / PLSQL returns a value > 0 and <=1.

The CUME_DIST function can be used in as an Aggregate and Analytical Function.

Syntax for the CUME_DIST function as an Aggregate Function in Oracle SQL / PLSQL is:
SELECT column(s),
CUME_DIST(value1, value2 , . , valueN)
WITHIN GROUP (ORDER BY column1, column2, . . , columnN)
FROM table_name
GROUP BY column(s);

Note:

• The Number of values / expressions in the CUME_DIST and the ORDER BY clause shoud be the same.
• The values / expressions in CUME_DIST and ORDER BY clause are matched based on positions and hence the data types must be compatible between both

Example 1:

Using CUME_DIST as AGGREGATE function
Suppose we have a table named ‘employee’ as shown below:

If we write our query as:

 SELECT CUME_DIST (18000) WITHIN GROUP (ORDER BY salary) CUME_DISTT
FROM employee;

We will get the following output:

The above SQL query returns the cumulative distribution of an employee having salary of 18000 within the employee table.

Example 2:
Using CUME_DIST as AGGREGATE function with GROUP BY clause

Suppose we have a table named ‘employee’ as shown below:

If we write our query as:

SELECT department
       ,CUME_DIST (18000) WITHIN GROUP (ORDER BY salary) CUME_DISTT
FROM employee
GROUP BY department; 

We will get the following output:

The above SQL query returns the cumulative distribution of an employee having salary of 18000 within the employee table grouped by departments