• 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 Analytical Function in Oracle SQL – PLSQL

CUME_DIST as Analytical 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 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


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