how to use cume_dist function in oracle 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

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

CUME_DIST as Analytical Function in Oracle SQL – PLSQL

CUME_DIST as Aggregate Function in Oracle SQL – PLSQL