What is Oracle CUBE ?

• In addition to the subtotals generated by the ROLLUP extension, the CUBE extension will generate subtotals for all combinations of the dimensions specified.

oracle tutorial , sql tutorial , sql , pl sql tutorial , oracle , pl sql , plsql

Syntax:

SELECT Column_1,
Column_2,
SUM(some_column) AS some_column
FROM table_name
GROUP BY CUBE (Column_1,Column_2,..)
ORDER BY column_1, Column_2;

click below button to copy the code. By - oracle tutorial - team

Copy Code

Example:

SELECT id,
       name,
       SUM(salary) AS salary 
FROM   wikitechy_employee
GROUP BY CUBE (id, city)
ORDER BY id, name;

click below button to copy the code. By - oracle tutorial - team

Copy Code

Sample Database:

• For better understanding of cube concept we are using a Wikitechy_employee table.

Code Explanation:

• The select list contains the dimension columns and aggregate function expressions selected from wikitechy_employee table.
• The CUBE operator is specified in the GROUP BY clause of a SELECT statement.
• The GROUP BY specifies the dimension columns and the keywords WITH CUBE.

Oracle cubes-data-warehousing-data-mart

Cube Example Output:

1. This row report the subtotal for the City dimension. It have null in the ID dimension to show that the aggregate data came from row having any value for the ID dimension.
2. This row depicts the total value of each city.

This tutorial provides an indepth knowledge on the following items such as oracle tutorial , sql tutorial , sql , pl sql tutorial , oracle , pl sql , mysql tutorial , sql tutorial for beginners , learn sql , oracle database tutorial , sql query tutorial , oracle dba tutorial , plsql tutorial , oracle tutorial pdf , oracle pl sql tutorial , oracle sql tutorial , sql tutorial point , oracle tutorial for beginners , learn oracle online free , learn oracle online , learning pl sql programming , learn sql online for free , sql learning online , dba oracle tutorial , oracle sql tutorial advanced , oracle 11g dba tutorial with examples , oracle online learning , oracle learning online , how to learn pl sql , sql coding tutorial , sql learning websites , sql basic learning