Outdated Version

You are viewing an older version of this section. View current production version.

CUBE and ROLLUP

The CUBE and ROLLUP operations let you group by different subsets of a specified set of columns in a SELECT clause. CUBE(column_list) groups by all subsets of the columns specified in column_list. For example, CUBE(a, b) groups by (a), (b), (a, b) and () (which represents the entire input set of rows). ROLLUP(column_list) groups by all subsets, inclucing the entire set of input rows, (), then the leftmost column, the two leftmost columns, and so on. For example, ROLLUP(a, b) groups by (), (a), and (a, b).

Syntax

Refer to the definition of the SELECT clause for where extended_grouping_expr can be used.

extended_grouping_expr ::= 
    CUBE ( col_name | expr | position}, ...)
  | ROLLUP ( col_name | expr | position}, ... )

Remarks

Output rows for CUBE and ROLLUP contain a NULL value in the position of a grouping column if that row represents an aggregate result that does not group by that column.

Examples

The following table contains some hypothetical sales quantities for products sold by state.

CREATE TABLE sales(state VARCHAR(30), product_id INT, quantity INT);
INSERT sales VALUES
  ("Oregon", 1, 10), ("Washington", 1, 15), ("California", 1, 40),
  ("Oregon", 2, 15), ("Washington", 2, 25), ("California", 2, 70);

This query uses CUBE to group by all subsets of columns in (state, product_id):

SELECT state, product_id, SUM(quantity)
FROM sales
GROUP BY CUBE(state, product_id)
ORDER BY state, product_id;

+------------+------------+---------------+
| state      | product_id | SUM(quantity) |
+------------+------------+---------------+
| NULL       |       NULL |           175 |
| NULL       |          1 |            65 |
| NULL       |          2 |           110 |
| California |       NULL |           110 |
| California |          1 |            40 |
| California |          2 |            70 |
| Oregon     |       NULL |            25 |
| Oregon     |          1 |            10 |
| Oregon     |          2 |            15 |
| Washington |       NULL |            40 |
| Washington |          1 |            15 |
| Washington |          2 |            25 |
+------------+------------+---------------+

The first row above represents the grand total. The second row represents the total for product 1 across all states. The fifth row represents the total for product 1 in California.

This query is the same as the one above but uses ROLLUP instead of CUBE:

SELECT state, product_id, SUM(quantity)
FROM sales
GROUP BY ROLLUP(state, product_id)
ORDER BY state, product_id;

+------------+------------+---------------+
| state      | product_id | SUM(quantity) |
+------------+------------+---------------+
| NULL       |       NULL |           175 |
| California |       NULL |           110 |
| California |          1 |            40 |
| California |          2 |            70 |
| Oregon     |       NULL |            25 |
| Oregon     |          1 |            10 |
| Oregon     |          2 |            15 |
| Washington |       NULL |            40 |
| Washington |          1 |            15 |
| Washington |          2 |            25 |
+------------+------------+---------------+

Notice that the results are the same as for CUBE except that there is no total for product_id 1 and product_id 2 across all states.

GROUPING() Function

The GROUPING(column_name) can be used in the output column list of a SELECT block to show whether an output row of a query with a GROUP BY clause has aggregated together all the input rows for different values of column_name. The output of GROUPING(column_name) is 1 if the result row has grouped together rows for column_name and 0 otherwise. For example:

SELECT state, product_id, SUM(quantity), GROUPING(state), GROUPING(product_id)
FROM sales
GROUP BY CUBE(state, product_id)
ORDER BY state, product_id;

+------------+------------+---------------+-----------------+----------------------+
| state      | product_id | SUM(quantity) | GROUPING(state) | GROUPING(product_id) |
+------------+------------+---------------+-----------------+----------------------+
| NULL       |       NULL |           175 |               1 |                    1 |
| NULL       |          1 |            65 |               1 |                    0 |
| NULL       |          2 |           110 |               1 |                    0 |
| California |       NULL |           110 |               0 |                    1 |
| California |          1 |            40 |               0 |                    0 |
| California |          2 |            70 |               0 |                    0 |
| Oregon     |       NULL |            25 |               0 |                    1 |
| Oregon     |          1 |            10 |               0 |                    0 |
| Oregon     |          2 |            15 |               0 |                    0 |
| Washington |       NULL |            40 |               0 |                    1 |
| Washington |          1 |            15 |               0 |                    0 |
| Washington |          2 |            25 |               0 |                    0 |
+------------+------------+---------------+-----------------+----------------------+

The first row of output has GROUPING(state) = 1 and GROUPING(product_id) = 1 because the row represents the grand total, meaning that rows for all state and product_id values have been combined together to form this row. The second row has GROUPING(state) = 1 but GROUPING(product_id) = 0 because it represents a summary across all state values for a specific product_id.