• 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 / clause / ROLLUP Clause in Oracle SQL – PLSQL

ROLLUP Clause in Oracle SQL – PLSQL

November 8, 2012 by techhoneyadmin

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.

Filed Under: clause Tagged With: how to use rollup clause in oracle database query, how to use rollup clause in oracle plsql, how to use rollup clause in oracle sql, rollup clause in oracle plsql, rollup clause in oracle sql, ROLLUPPLSQL, syntax and example of rollup clause in oracle database query, syntax and example of rollup clause in oracle plsql, syntax and example of rollup clause in oracle sql, using rollup clause in oracle database query, using rollup clause in oracle plsql, using rollup clause in oracle sql

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