6.3. 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.

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 2n groupings.

6.3.1. ROLLUP

The boss asks Janice to give him a report that breaks down the 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)

6.3.2. CUBE

The report that Janice wrote for King using the ROLLUP operator was fine—until he wanted to know summaries by job title also. Janice realizes 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)