Tech Honey

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

CUME_DIST as Aggregate Function in Oracle SQL – PLSQL

by

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

CUBE in Oracle SQL – PLSQL

by

The CUBE in Oracle SQL / PLSQL is an extension for the GROUP BY clause.

Syntax for CUBE in Oracle SQL / PLSQL is:
SELECT column(s),
AGGREGATE_FUNCTION(s),
FROM table_name
GROUP BY CUBE column(s)
[ORDER BY column(s)];

Example:

Using CUBE in Oracle SQL / PLSQL Query
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
       ,commission
       ,SUM(salary) Total_Salary
FROM employee
GROUP BY CUBE (department, commission);

We will get the following output:

Department Commission Total_Salary
IT 20 48000
IT 48000
Sales 10 82000
Sales 82000
Support 5 30000
Support 30000
5 30000
10 82000
20 48000
160000

Let’s understand the fetched results to understand CUBE:

  • 1st, 3rd and 5th row tells us the commission and the total salary grouped by IT, Sales and Support department along with the respective commissions
  • 2nd, 4th and 6th row tells us the total salary in the IT, Sales and Support department neglecting the commissions being given to the employees.
  • 7th, 8th and 9th row tells us the total salary being given to the employees having commissions of 5 , 10 and 20 respectively neglecting the departments in which they work.
  • The 10th row tells us the total salary being given to the employees, neglecting the department and the commissions of the employees.

UNBOUNDED PRECEDING Clause with PARTITION BY in Oracle SQL – PLSQL

by

The UNBOUNDED PRECEDING is a windowing clause which defines the aggregation window i.e. the extent of rows to be used in aggregation. It tells oracle, the extent from where the rows are to be aggregated in the subgroup.

Syntax for the UNBOUNDED PRECEDING clause with PARTITION BY in Oracle SQL / PLSQL is:
SELECT columns
,aggregate_function () OVER (PARTITION BY column(s) ORDER BY column(s) ROWS UNBOUNDED PRECEDING)
FROM table_name
GROUP BY (column(s));

Example:
Using the UNBOUNDED PRECEDING clause with partition by
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
       ,employee_name
       ,SUM(salary) EMPLOYEE_SALARY
       ,SUM(SUM(Salary)) OVER (PARTITION BY department ORDER BY department
                         ROWS UNBOUNDED PRECEDING) AS TOTAL_SALARY
FROM employee
GROUP BY department, salary, employee_name
ORDER BY department, employee_name;

We will get the following result:

Department Employee_Name Employee_Salary Dept_Salary
IT Emp B 20000 20000
IT Emp C 28000 48000
Sales Emp A 10000 10000
Sales Emp E 32000 42000
Sales Emp F 40000 82000
Support Emp D 30000 30000

Let’s observe the records (especially the ‘Dept_Salary’ column) fetched to understand the UNBOUNDED PRECEDING clause.

  • 1st and 2nd record (for Emp B and Emp C) pertain to ‘IT’ department, the Dept_salary column for the second records gives us the total of the salary being given to the ‘Emp B’ and ‘Emp C’ in ‘IT’ Department
  • 3rd, 4th and 5th records (for Emp A, Emp E amd Emp F) correspond to Sales department.
  • The Dept_salary column of the 4th and 5th record tells us the total of salary being given to  ‘Emp A’ and ‘Emp E’ and ‘Emp A’, ‘Emp E’ and ‘Emp F’ respectively in ‘Sales’ department.
  • The 6th record corresponds to ‘Emp D’ in ‘Support’ department, the Dept_salary column for this record gives the total of salary being given to ‘Emp D’in ‘Support’ department.

UNBOUNDED PRECEDING Clause without PARTITION BY in Oracle SQL – PLSQL

by

The UNBOUNDED PRECEDING is a windowing clause which defines the aggregation window i.e. the extent of rows to be used in aggregation. It tells oracle, the extent from where the rows are to be aggregated in the subgroup.

Syntax for the UNBOUNDED PRECEDING clause without PARTITION BY in Oracle SQL / PLSQL is:
SELECT columns
,aggregate_function () OVER (ORDER BY column(s) ROWS UNBOUNDED PRECEEDING)
FROM table_name
GROUP BY (column(s));

Example:

Using the UNBOUNDED PRECEDING clause without PARTITION BY

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
       ,employee_name
       ,SUM(salary) EMPLOYEE_SALARY
       ,SUM(SUM(Salary)) OVER (ORDER BY department ROWS UNBOUNDED PRECEDING) AS CUMULATIVE_SALARY
FROM employee
GROUP BY department, salary, employee_name
ORDER BY department, employee_name;

We will get the following result:

Department Employee_Name Employee_Salary Cumulative_Salary
IT Emp B 20000 20000
IT Emp C 28000 48000
Sales Emp A 10000 58000
Sales Emp E 32000 90000
Sales Emp F 40000 130000
Support Emp D 30000 160000

Let’s observe the records (especially the ‘Cumulative_Salary’ column) fetched to understand the UNBOUNDED PRECEDING clause.

  • 1st and 2nd record (for Emp B and Emp C) pertain to ‘IT’ department, the cumulative_salary column for the second records gives us the total of the salary being given to the ‘Emp B’ and ‘Emp C’
  • 3rd, 4th and 5th records (for Emp A, Emp E amd Emp F) correspond to Sales department.
  • The ‘cumulative_salary’ column in the 3rd record tells us the total of the salary being given to ‘Emp B’, ‘Emp C’ and ‘Emp A’. Similarly the cumulative_salary culomn of the 4th and 5th record tells us the total of salary being given to ‘Emp B’, ‘Emp C’, ‘Emp A’ and ‘Emp E’ and ‘Emp B’, ‘Emp C’, ‘Emp A’, ‘Emp E’ and ‘Emp F’ respectively.
  • The 6th record corresponds to ‘Emp D’ in ‘Support’ department, the cumulative_salary column for this record gives the total of salary being given to ‘Emp A’, ‘Emp B’, ‘Emp C’, ‘Emp D’, ‘Emp E, and ‘Emp F’.

ROLLUP Clause in Oracle SQL – PLSQL

by

In simple terms the ROLLUP clause is used to get the subtotal and grand total in a set of fetched records based on groups.

In other words we can say that ROLLUP clause extends the functionality of the GROUP BY Clause by returning rows containing a subtotal for each group along with a grand total for all groups

Syntax for the ROLLUP clause in Oracle SQL / PLSQL is:
SELECT columns
,aggregate_function ()
FROM table_name
GROUP BY ROLLUP(column(s));

Example:

Using the ROLLUP 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 employee_name
       ,count(*) Employees
       ,department
FROM employee
GROUP BY ROLLUP(department,employee_name);

We will get the following result:

Employee_Name Employees Department
Emp B 1 IT
Emp C 1 IT
2 IT
Emp A 1 Sales
Emp E 1 Sales
Emp F 1 Sales
3 Sales
Emp D 1 Support
1 Support
6

Let’s observe the records fetched to understand the ROLLUP clause:

  1. 1st and 2nd record (for Emp B and Emp C) pertain to ‘IT’ department
  2. The 3rd record is the subtotal for the IT department; it states that the total number of employees in ‘IT’ department is 2.
  3. 4th, 5th and 6th records (for Emp A, Emp E amd Emp F) correspond to Sales department.
  4. The 7th record is the subtotal for the ‘Sales’ department and tells us that there are 3 employees in ‘Sales’ department.
  5. 8th record for Emp D corresponds to the “Support’ department
  6. 9th record is the subtotal for the ‘Support’ department and tells us that there is only 1 employee in ‘Support’ department.
  7. Finally the 10th record is the grand total of all the records and tells us that there are total of 6 employees in ‘IT’, ‘Sales’ and ‘Support’ department  combined together.