• 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 / sql / CUBE in Oracle SQL – PLSQL

CUBE in Oracle SQL – PLSQL

November 19, 2012 by techhoneyadmin

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.

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

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