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:

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 CUME_DIST (18000) WITHIN GROUP (ORDER BY salary) CUME_DISTT
FROM employee;

We will get the following output:

CUME_DISTT
0.285714285714286

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:

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
       ,CUME_DIST (18000) WITHIN GROUP (ORDER BY salary) CUME_DISTT
FROM employee
GROUP BY department; 

We will get the following output:

Department CUME_DISTT
IT 0.333333333333333
Sales 0.5
Support 0.5

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


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