In the previous posts, we have seen two examples that add additional rows with summarized values to the result of a query. The first post demonstrated a basic example without going deep into the aggregation functions. However, the second example demonstrated a little more complex example using WITH ROLLUP and explained the usage of the GROUPING() function.

In this post we will examine the difference between WITH ROLLUP and WITH CUBE.

The only difference between WITH ROLLUP and WITH CUBE is that, WITH CUBE adds additional rows to the query result. WITH CUBE will add an additional row to the query results for each unique combination of the columns used in the grouping. Let us see an example to understand this.

Let us take the sample data we used for the previous example.

IF OBJECT_ID('Orders') IS NOT NULL DROP TABLE Orders
GO
CREATE TABLE Orders (
    OrderID INT,
    OrderDate DATETIME,
    CustomerName VARCHAR(20),
    ItemName VARCHAR(20),
    Quantity INT,
    PricePerCase MONEY )
GO

INSERT INTO Orders( OrderID, OrderDate, CustomerName, ItemName, Quantity, PricePerCase )
    SELECT 1, '2008-08-01', 'Jacob', 'Item 1', 10, 12.5
INSERT INTO Orders( OrderID, OrderDate, CustomerName, ItemName, Quantity, PricePerCase )
    SELECT 1, '2008-08-01', 'Jacob', 'Item 2', 12, 15.0
INSERT INTO Orders( OrderID, OrderDate, CustomerName, ItemName, Quantity, PricePerCase )
    SELECT 2, '2008-08-02', 'Jacob', 'Item 1', 15, 12.5
INSERT INTO Orders( OrderID, OrderDate, CustomerName, ItemName, Quantity, PricePerCase )
    SELECT 2, '2008-08-02', 'Jacob', 'Item 2', 20, 15.0
INSERT INTO Orders( OrderID, OrderDate, CustomerName, ItemName, Quantity, PricePerCase )
    SELECT 1, '2008-08-01', 'Mike', 'Item 1', 6, 12.5
INSERT INTO Orders( OrderID, OrderDate, CustomerName, ItemName, Quantity, PricePerCase )
    SELECT 1, '2008-08-01', 'Mike', 'Item 2', 4, 11.0

Let us first try WITH ROLLUP and generate a total of each item purchased by each customer. Then let us see the version of the same query using WITH CUBE and see the differences.

SELECT
    CASE 
        WHEN GROUPING(CustomerName) = 1 THEN 'All Customers' 
        ELSE CustomerName 
    END AS CustomerName,
    CASE WHEN GROUPING(ItemName) = 1 THEN 'All Items'
        ELSE ItemName
    END AS ItemName,
    SUM(Quantity * PricePerCase) AS Amount
FROM Orders
GROUP BY CustomerName, ItemName
WITH ROLLUP

/*
CustomerName         ItemName             Amount
-------------------- -------------------- ---------------------
Jacob                Item 1               312.50
Jacob                Item 2               480.00
Jacob                All Items            792.50
Mike                 Item 1               75.00
Mike                 Item 2               44.00
Mike                 All Items            119.00
All Customers        All Items            911.50
*/

Now let us see the version of the query using WITH CUBE.

SELECT
    CASE 
        WHEN GROUPING(CustomerName) = 1 THEN 'All Customers' 
        ELSE CustomerName 
    END AS CustomerName,
    CASE WHEN GROUPING(ItemName) = 1 THEN 'All Items'
        ELSE ItemName
    END AS ItemName,
    SUM(Quantity * PricePerCase) AS Amount
FROM Orders
GROUP BY CustomerName, ItemName
WITH CUBE

/*
CustomerName         ItemName             Amount
-------------------- -------------------- ---------------------
Jacob                Item 1               312.50
Jacob                Item 2               480.00
Jacob                All Items            792.50
Mike                 Item 1               75.00
Mike                 Item 2               44.00
Mike                 All Items            119.00
All Customers        All Items            911.50
All Customers        Item 1               387.50
All Customers        Item 2               524.00
*/

Note the two additional rows returned by WITH CUBE. It generated a row for ALL customers + Item 1 and All Customers + Item 2. WITH CUBE will generate an additional row for the unique combination of each column used in the grouping.

Read Other Posts In this series

1. TSQL Lab 4 - How to add a total line to the query result
2. TSQL Lab 5 - Using WITH ROLLUP to generate Subtotals and Grand Total rows
3. TSQL Lab 6 - Using WITH CUBE to generate subtotal and grand total rows
4. TSQL Lab 7 - Enhancements to ROLLUP and CUBE in SQL Server 2008
5. TSQL Lab 8 - GROUPING SETS() - New Grouping function introduced in SQL Server 2008

SEE LABELS

1. COMPUTE BY
2. WITH CUBE
3. WITH ROLLUP
4. TSQL Labs
5. GROUPING SETS
6. GROUPING