CUME_DIST as Analytical 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 Analytical Function in Oracle SQL / PLSQL is: SELECT column(s),
CUME_DIST() OVER (PARTITION BY column ORDER BY column(s))
FROM table_name;

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

Employee_Id Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10
106 Emp F 40000 Sales 10

If we write our query as:

SELECT department
       ,salary
       ,CUME_DIST () OVER (PARTITION BY department ORDER BY salary) CUME_DISTT
FROM employee; 

We will get the following output:

Department Salary CUME_DISTT
IT 20000 0.5
IT 28000 1
Sales 10000 0.333333333333333
Sales 32000 0.666666666666667
Sales 40000 1
Support 30000 1

The above SQL query returns the cumulative distribution of employees within the employee table grouped by departments


Tagged , , , , , , , , , , , . Bookmark the permalink.