ROLLUP and CUBE

Sometimes, a simple GROUP BY clause just isn’t enough in a query. Once you generate a report of, let’s say, average salary by department or the standard deviation of sick days by job title, you often must run a second query that calculates the average salary or standard deviation across the entire set of employees. It gets even more complex when you break down the average salary by more than one factor, such as department and job title. In this case, you would need to run two or more additional queries to produce the average salary just by department or for the entire workforce.

Tip

The results from both CUBE and ROLLUP can be produced by multiple queries, but this requires multiple passes over the rows in the table. CUBE and ROLLUP need only one pass.

The ROLLUP operator provides rollups of aggregate functions in one direction across the fields that are aggregated. For each ROLLUP operation that uses n columns, the result set has aggregates for each combination of columns and n+1 groupings.

The CUBE operator takes the ROLLUP operator a step further and provides rollups of aggregate functions in both directions across the fields that are to be aggregated. For each CUBE operation that uses n columns, the result set has aggregates for each combination of columns plus 2ⁿ groupings.

ROLLUP

The boss asks Janice to give him a report that breaks down average salary by both department and job function for departments 10 through 90. Janice wants to save time writing the query, and she knows by now that King will want to see some subtotals and grand totals. She will use ROLLUP to accomplish the task in a single query, as follows:

select department_id "Dept", job_id "Job",        avg(salary) "Avg Sal" from employees where department_id between 10 and 90 group by rollup(department_id, job_id);   Dept Job           Avg Sal ------ ---------- ----------     10 AD_ASST          4400     10                  4400     20 MK_MAN          13000     20 MK_REP           6000     20                  9500     30 PU_MAN          11000     30 PU_CLERK         2780     30                  4150     40 HR_REP           6500     40                  6500     50 ST_MAN           7280     50 SH_CLERK         3215     50 ST_CLERK         2785     50            3475.55556     60 IT_PROG          5760     60                  5760     70 PR_REP          10000     70                 10000     80 SA_MAN          12200     80 SA_REP     8396.55172     80            8955.88235     90 AD_VP           17000     90 AD_PRES         24000     90            19333.3333                         6250 25 rows selected.

Notice that because Janice has two columns listed in her ROLLUP clause, she will have three (two plus one) types of groupings in the query output:

Combinations of departments and jobs (for example, 30 and PU_CLERK, with an average salary of 2780)
Summaries by departments (for example, 20 and a NULL job title, with an average salary of 9500)
A grand total (NULL department number and NULL job title, with an average salary for all employees in all departments of 6250)

CUBE

The report that Janice wrote for King using the ROLLUP was fine—until he wanted to know summaries by job title also. Janice realized that she should have given him the version of the query using CUBE to begin with, so she changes her previous query, substituting the keyword CUBE for ROLLUP:

select department_id "Dept", job_id "Job",        avg(salary) "Avg Sal" from employees where department_id between 10 and 90 group by cube(department_id, job_id);   Dept Job           Avg Sal ------ ---------- ----------                         6250        AD_VP           17000        HR_REP           6500        MK_MAN          13000        MK_REP           6000        PR_REP          10000        PU_MAN          11000        SA_MAN          12200        SA_REP     8396.55172        ST_MAN           7280        AD_ASST          4400        AD_PRES         24000        IT_PROG          5760        PU_CLERK         2780        SH_CLERK         3215        ST_CLERK         2785     10                  4400     10 AD_ASST          4400     20                  9500     20 MK_MAN          13000     20 MK_REP           6000     30                  4150     30 PU_MAN          11000     30 PU_CLERK         2780     40                  6500     40 HR_REP           6500     50            3475.55556     50 ST_MAN           7280     50 SH_CLERK         3215     50 ST_CLERK         2785     60                  5760     60 IT_PROG          5760     70                 10000     70 PR_REP          10000     80            8955.88235     80 SA_MAN          12200     80 SA_REP     8396.55172     90            19333.3333     90 AD_VP           17000     90 AD_PRES         24000 40 rows selected.

Using CUBE, she has two columns listed in our ROLLUP clause, and therefore will have four (two squared) types of groupings in the query output:

Combinations of departments and jobs (for example, 30 and PU_CLERK, with an average salary of 2780)
Summaries by jobs (for example, MK_REP having an average salary of 6000)
Summaries by departments (for example, 20 and a NULL job title, with an average salary of 9500)
A grand total (NULL department number and NULL job title, with an average salary for all employees in all departments of 6250)