CUBE in SQL Server

in this tutorial, you will learn how to use the SQL Server CUBE to generate multiple grouping sets.

Introduction to SQL Server CUBE

Grouping sets specify groupings of data in a single query. For example, the following query defines a single grouping set represented as (brand):

SELECT 
    brand, 
    SUM(sales)
FROM 
    sales.sales_summary
GROUP BY 
    brand;

If you have not followed the GROUPING SETS tutorial, you can create the sales.sales_summary table by using the following query:

SELECT
    b.brand_name AS brand,
    c.category_name AS category,
    p.model_year,
    round(
        SUM (
            quantity * i.list_price * (1 - discount)
        ),
        0
    ) sales INTO sales.sales_summary
FROM
    sales.order_items i
INNER JOIN production.products p ON p.product_id = i.product_id
INNER JOIN production.brands b ON b.brand_id = p.brand_id
INNER JOIN production.categories c ON c.category_id = p.category_id
GROUP BY
    b.brand_name,
    c.category_name,
    p.model_year
ORDER BY
    b.brand_name,
    c.category_name,
    p.model_year;

Even though the following query does not use the GROUP BY clause, it generates an empty grouping set which is denoted as ().

SELECT 
    SUM(sales)
FROM 
    sales.sales_summary;

The CUBE is a subclause of the GROUP BY clause that allows you to generate multiple grouping sets. The following illustrates the general syntax of the CUBE:

SELECT
    d1,
    d2,
    d3,
    aggregate_function (c4)
FROM
    table_name
GROUP BY
    CUBE (d1, d2, d3);          

In this syntax, the CUBE generates all possible grouping sets based on the dimension columns d1, d2, and d3 that you specify in the CUBE clause.

The above query returns the same result set as the following query, which uses the  GROUPING SETS:

SELECT
    d1,
    d2,
    d3,
    aggregate_function (c4)
FROM
    table_name
GROUP BY
    GROUPING SETS (
        (d1,d2,d3), 
        (d1,d2),
        (d1,d3),
        (d2,d3),
        (d1),
        (d2),
        (d3), 
        ()
     );

If you have N dimension columns specified in the CUBE, you will have 2^N grouping sets.

It is possible to reduce the number of grouping sets by using the CUBE partially as shown in the following query:

SELECT
    d1,
    d2,
    d3,
    aggregate_function (c4)
FROM
    table_name
GROUP BY
    d1,
    CUBE (d2, d3);

In this case, the query generates four grouping sets because there are only two dimension columns specified in the CUBE.

SQL Server CUBE examples

The following statement uses the CUBE to generate four grouping sets:

1. (brand, category)
2. (brand)
3. (category)
4. ()

SELECT
    brand,
    category,
    SUM (sales) sales
FROM
    sales.sales_summary
GROUP BY
    CUBE(brand, category);

In this example, we have two dimension columns specified in the CUBE clause, therefore, we have a total of four grouping sets.

The following example illustrates how to perform a partial CUBE to reduce the number of grouping sets generated by the query:

SELECT
    brand,
    category,
    SUM (sales) sales
FROM
    sales.sales_summary
GROUP BY
    brand,
    CUBE(category);