Lesson 31 : CUBE in SELECT

CUBE in SELECT Queries 

 CUBE enables  SELECT statement  to set of grouping columns and creates subtotals for all of their possible combinations. In terms of multidimensional analysis, CUBE generates all the subtotals that could be calculated for a data cube with the specified dimensions.

If  we  specify CUBE in SELECT, the result set will include all the values that would be included in an equivalent ROLLUP statement plus additional combinations.

In short, the CUBE is extension of ROLLUP and it will generate subtotals for all combinations of the dimensions specified. If "n" is the number of columns listed in the CUBE, there will be 2n subtotal combinations

Syntax :

SELECT col1, group function(col2), .. FROM <tablename> GROUP  BY  CUBE(column list)

Examples :

SQL> create table employee(name varchar(10),dept_id number(3),location varchar(10),salary number(5));

Table created.

SQL> insert into employee values('John',10,'Dallas',50000);

1 row created.

SQL> insert into employee values('Devy',10,'Dallas',75000);

1 row created.

SQL> insert into employee values('Paul',20,'Texas',85000);

1 row created.

SQL> insert into employee values('Raj',20,'Texas',65000);

1 row created.

SQL> insert into employee values('Chetan',30,'Boston',95000);

1 row created.

SQL> insert into employee values('Jayne',30,'Boston',55000);

1 row created.

SQL> insert into employee values('Senthil',30,'Chennai',45000);

1 row created.

SQL> insert into employee values('Jeya',30,'Chennai',25000);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from employee;

NAME          DEPT_ID LOCATION       SALARY

---------- ---------- ---------- ----------
John             10 Dallas          50000
Devy            10 Dallas          75000
Paul             20 Texas           85000
Raj               20 Texas           65000
Chetan       30 Boston         95000
Jayne          30 Boston         55000
Senthil       30 Chennai       45000
Jeya           30 Chennai       25000

8 rows selected.

SQL> select location,dept_id,count(*),sum(salary) from employee group by (location,dept_id);

 LOCATION      DEPT_ID   COUNT(*) SUM(SALARY)
---------- ---------- ---------- -----------
Texas              20          2      150000
Boston           30          2      150000
Dallas             10          2      125000
Chennai         30          2       70000

SQL> SQL> select location,dept_id,count(*),sum(salary) from employee group by cube(location,dept_id);

LOCATION      DEPT_ID   COUNT(*) SUM(SALARY)

---------- ---------- ---------- -----------
                               8      495000
                   10       2      125000
                   20       2      150000
                   30       4      220000
Texas                    2      150000
Texas        20       2      150000
Boston                 2      150000
Boston     30       2      150000
Dallas                  2      125000
Dallas       10      2      125000
Chennai             2       70000
Chennai   30     2       70000

12 rows selected.