Cubes in Oracle

The issue about roll ups in Oracle is that roll up only calculate sub totals in one direction. So if you have a country, city combination, subtotals are calculated per city in a country, but not in a country per city.

In case of a hierarchy, this is ok. One is only interested in having the subtotals in a certain natural order. First totals per city, then totals per country and finally totals per region.

It is different if not such natural order exists. Think of a combination of department and location. A department may be spread over several locations and a location may have several departments. One could be interested to have to subtotals per department (aggregating over locations) and subtotals per location (aggregating over departments).

For that situation, we have the concept of a cube.

select department_id ,job_id, count(employee_id) emp_count 
FROM
employees
group by cube (department_id ,job_id) ;

So instead of “rollup”, the keyword “cube” is used.

Like the rollup, we may add a grouping clause to indicate how the subtotals are calculated.

select  department_id ,  job_id , count(*) ,
 GROUPING ( department_id ) g1,
 GROUPING ( job_id ) g2
 from employees
 group by  cube( department_id ,  job_id );

or

select  department_id ,  job_id , count(*) ,
GROUPING_id (department_id,job_id) group_id
from employees
group by  cube( department_id ,  job_id );

or

select  department_id ,  job_id , count(*) ,
GROUPING_id (department_id,job_id) group_id
from employees
group by
GROUPING SETS  ((department_id,job_id ) , () );

Note that the latter SQL does not contain a cube clause. Instead, the GROUPING SETS is used. This allows us to explicitly mention all combinations for which a sub total is required. Here, the grand total is required (therefore ()), with the subtotals on low level (department_id, job_id).