Structured Query Language/Rollup Cube

In the chapter Grouping we have seen that the key word GROUP BY creates groups of rows within a result set. Additionally aggregat functions like  computes condensed values for each of those groups.

Because GROUP BY can summarize by multiple columns, there is often the requirement to compute summary values for 'super-groups', which arise by successively omitting one column after the next from the GROUP BY specification.

Example Table
To illustrate the situation, we offer an example table and typical questions to such kind of tables.

In the table, there are two different car producers, 6 models and 4 years. Typical questions to such tables are:
 * Number of cars per producer or per model.
 * Number of cars per combination of some criteria like: producer plus model or producer plus year.
 * Total number of cars (without any criteria).

ROLLUP
As we have seen, the keyword GROUP BY offers condensed data for exactly one grouping level, producer plus model in this case.

In such situations, one would like to know also the corresponding values for upper groups: per producer or for the whole table. This can be achieved by submitting a slightly different SELECT.

In principle, it is possible, to combine such SELECTs via UNION or to submit them sequentially. But because this is a standard requirement, SQL offers a more elegant solution, namely the extension of the GROUP BY with the ROLLUP keyword. Based on the results of the GROUP BY, it offers additional rows for every superordinate group, which arises by omitting the grouping criteria one after the other. The simple GROUP BY clause creates rows at the level of producer plus model. The ROLLUP keyword leads to additional rows where first the model and then model and producer are omitted.

CUBE
The ROLLUP keyword offers solutions where a hierarchical point of view is adequate. But in data warehouse applications, one likes to navigate freely through the aggregated data, not only from top to bottom. To support this requirement, the SQL standard offers the keyword CUBE. It is an extension of ROLLUP and offers additional rows for all possible combinations of the GROUP BY columns.

In the case of our above example with the two columns producer and model, the ROLLUP has created rows for 'producer-only' and 'no criteria' (= complete table). Additional to that, CUBE creates rows for &apos;model-only'. (If different producer would use the same model-name, such rows will lead to only 1 additional row.)

If there are tree grouping columns c1, c2, and c3, the keywords lead to the following grouping.