BlackWaspTM

This web site uses cookies. By using the site you accept the cookie policy.This message is for compliance with the UK ICO law.

SQL Server
SQL 2005+

SQL Server Summaries with ROLLUP and CUBE

The twenty-ninth part of the SQL Server Programming Fundamentals tutorial describes the ROLLUP and CUBE clauses, which can be used when grouping data. These clauses create additional summary rows containing aggregated values from multiple groups of rows.

Using the CASE Statement and Grouping Function to Replace NULL Values

With the use of the Grouping function, we can determine whether a NULL has been generated for a grouping column by the summarising process. We can use this knowledge to replace the NULL with a more suitable value when necessary. To do this we will use a conditional statement named CASE. I will not describe the CASE statement in detail in this article; as it is covered later in the tutorial. In this instance we will simply describe one usage, which allows a value to be tested and used to determine which of two other values to return in the results.

We will use two CASE statements. The first will be for the PartNumber column. The condition being checked, or predicate, will be whether the Grouping function for the column returns 1. If it does, the literal text, 'ALL ENGINEERS' will be returned for the row. If not, the column value from the table will be returned. The syntax for this condition is:

CASE WHEN grouping(S.PartNumber)=1 THEN 'ALL PARTS' ELSE S.PartNumber END

As you can see, the predicate appears after the WHEN clause. The two possible answers appear after the THEN and ELSE clauses. This makes the statement easy to read in English as "When the grouping value of the PartNumber column is 1 then return 'ALL PARTS'. Otherwise, return the value of the PartNumber column". The second CASE statement will be similar to the first, except that it will operate on the engineer's name.

To demonstrate, execute the following query. You should see several occurrences of the ALL ENGINEERS text and one grand total row containing both ALL PARTS and ALL ENGINEERS.

SELECT
    CASE WHEN grouping(S.PartNumber)=1
        THEN 'ALL PARTS' ELSE S.PartNumber END,
    CASE WHEN grouping(E.EngineerName)=1
        THEN 'ALL ENGINEERS' ELSE E.EngineerName END,
    sum(S.UnitsHeld)
FROM
    EngineerStock S
INNER JOIN
    Engineers E
ON
    S.EngineerId = E.EngineerId
GROUP BY
    S.PartNumber,
    E.EngineerName
WITH ROLLUP

NB: Sometimes you will find queries where the NULLs are replaced with standard text using the ISNULL function. This gives the correct results when the grouped columns cannot contain NULL values. However, if they can contain NULLs, these will be replaced too, potentially giving misleading results.

Using CUBE

For our final example we will use the WITH CUBE clause. This is similar to the WITH ROLLUP clause in its operation as it adds new summary rows for groups of results. However, rather than creating a hierarchy of results, a cube contains totals for every possible permutation of group. Returning to our pivot table layout, every total will be calculated.

Part / EngineerEngineer AEngineer BEngineer CTotals
Part 110162046
Part 2810725
Part 212112346
Totals303750117

To show the results, execute the following query. This is the same as the previous query except that it uses CUBE instead of ROLLUP. You can see that the result set contains subtotals for every engineer and for every part individually.

SELECT
    CASE WHEN grouping(S.PartNumber)=1
        THEN 'ALL PARTS' ELSE S.PartNumber END,
    CASE WHEN grouping(E.EngineerName)=1
        THEN 'ALL ENGINEERS' ELSE E.EngineerName END,
    sum(S.UnitsHeld)
FROM
    EngineerStock S
INNER JOIN
    Engineers E
ON
    S.EngineerId = E.EngineerId
GROUP BY
    S.PartNumber,
    E.EngineerName
WITH CUBE
4 June 2009