SQL 2005+

SQL Server Summaries with ROLLUP and CUBE (2)

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 (?).

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:

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