• Skip to primary navigation
  • Skip to main content

Tech Honey

The #1 Website for Oracle PL/SQL, OA Framework and HTML

  • Home
  • HTML
    • Tags in HTML
    • HTML Attributes
  • OA Framework
    • Item in OAF
    • Regions in OAF
    • General OAF Topics
  • Oracle
    • statement
    • function
    • clause
    • plsql
    • sql
You are here: Home / Oracle / function / CUME_DIST as Aggregate Function in Oracle SQL – PLSQL

CUME_DIST as Aggregate Function in Oracle SQL – PLSQL

November 19, 2012 by techhoneyadmin

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


Filed Under: function Tagged With: CUMEDISTPLSQL, cume_dist function in oracle plsql, cume_dist function in oracle sql, how to use cume_dist function in oracle database query, how to use cume_dist function in oracle plsql, how to use cume_dist function in oracle sql, syntax and example of cume_dist function in oracle database query, syntax and example of cume_dist function in oracle plsql, syntax and example of cume_dist function in oracle sql, using cume_dist function in oracle database query, using cume_dist function in oracle plsql, using cume_dist function in oracle sql

Copyright © 2023 · Parallax Pro on Genesis Framework · WordPress · Log in