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 Multiple Grouping Columns

The ROLLUP clause become more interesting when used in queries that have grouping across several columns. In these cases, the grouping columns are used to generate a hierarchy. The first grouping column is the root of the hierarchy and each subsequent grouping column adds another layer. Totals are then added at each level of the hierarchy.

For example, if we were to modify the previous query so that it grouped by the engineer's name, then by the stock item's part number, subtotals would be created for each engineer and a grand total created for all of the engineers in the results. These would be the rows added by the clause that are supplementary to the totals calculated for each combination of engineer and part.

You can picture this by placing the data into a simple pivot table. Below there are three engineers and three products. We can see grand totals for each engineer but not for each product. The missing totals are shown in the table as question marks (?).

Part / EngineerEngineer AEngineer BEngineer CTotals
Part 1101620?
Part 28107?
Part 2121123?
Totals303750117

To show this using the data from the JoBS database, execute the following query. This will not, of course, be displayed as a pivot table.

SELECT
    E.EngineerName,
    S.PartNumber,
    sum(S.UnitsHeld)
FROM
    EngineerStock S
INNER JOIN
    Engineers E
ON
    S.EngineerId = E.EngineerId
GROUP BY
    E.EngineerName,
    S.PartNumber
WITH ROLLUP

If the order of grouping is altered, the totals that are calculated also change. If we change the grouping to be by part first, then by engineer, we lose the engineer totals but gain summaries for each part. Again, we can see this by pivoting the data:

Part / EngineerEngineer AEngineer BEngineer CTotals
Part 110162046
Part 2810725
Part 212112346
Totals???117

The query for this is as follows, note the changed order of grouping columns.

SELECT
    S.PartNumber,
    E.EngineerName,
    sum(S.UnitsHeld)
FROM
    EngineerStock S
INNER JOIN
    Engineers E
ON
    S.EngineerId = E.EngineerId
GROUP BY
    S.PartNumber,
    E.EngineerName
WITH ROLLUP

The Grouping Function

When summarising information using the ROLLUP clause, SQL Server provides a new function named "Grouping". This function requires a single parameter containing the name of one of the grouping columns in the query. The function then returns either 1 or 0 for every row in the results set. If the named column is aggregated for the row, meaning that the rollup has inserted a NULL value into the column for that row, the value of the function will be 1. If not, it will be zero.

We can see the Grouping function in action by executing the following query. This query adds two columns containing the Grouping results for PartNumber and EngineerName. Scan the results to confirm that wherever a NULL is inserted by the ROLLUP, a corresponding 1 appears in the matching Grouping column.

SELECT
    S.PartNumber,
    E.EngineerName,
    sum(S.UnitsHeld),
    grouping(S.PartNumber) AS PartGrouping,
    grouping(E.EngineerName) AS EngineerGrouping
FROM
    EngineerStock S
INNER JOIN
    Engineers E
ON
    S.EngineerId = E.EngineerId
GROUP BY
    S.PartNumber,
    E.EngineerName
WITH ROLLUP
4 June 2009