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.
Summarising Grouped Data
In the previous article in this series we investigated the use of the GROUP BY clause to create queries that performed aggregation of data for groups of related rows. In the article I gave the example that you could use this grouping to calculate a business's sales income for each targeted region and list the results using a single query. Without the grouping the same aggregate functions could be used to calculate the total sales income for the entire business. However, to obtain both the subtotals and the overall total would require two separate queries to be executed. If there were multiple groups to calculate subtotals across, for example sales region and sales person, to obtain the subtotals required to populate a simple pivot table would require further queries.
In this article we will look at two clauses that can be added to a query containing a GROUP BY clause. The WITH ROLLUP and WITH CUBE clauses assist in summarising information in the manner described above. In addition to the totals for each grouped row, new rows are added to the results to provide grand totals. NB: Although the examples in this article use the "sum" aggregate function, the clauses can also be used with count, avg, min and max.
The examples in this article refer to the JoBS tutorial database. This is the database that has been created and populated with data throughout the course of this series of articles. If you do not have an up-to-date copy of the database you can create one using the script that may be downloaded via the link at the top of this page.
Let's start with a very simple example of the WITH ROLLUP clause that can be built upon as we progress through the article. If we wish to find the total number of stock items held by each engineer in the JoBS database, we can use a query with a GROUP BY clause. The query below obtains this information by summing the UnitsHeld value from the EngineerStock table, with grouping according to the engineer holding the stock.
S.EngineerId = E.EngineerId
The above query finds the subtotals of stock levels for each engineer but does not find the total stock held for all of the engineers combined. To obtain this, we must execute the aggregate function without any grouping, as follows:
SELECT sum(UnitsHeld) FROM EngineerStock
If we wished to combine the results from the two queries above, we could add a new first column to the second query, possibly using a literal VarChar value of 'Total'. This would make the column lists compatible so that the queries' result sets could be combined using a UNION. However, using two separate queries would be inefficient. Instead, we can use a rollup.
A Simple ROLLUP
When only one column is used for grouping, a rollup adds one new row to the query's output. This row contains aggregated values for the entire result set. If you are using the sum function, the new row includes the total of all of the rows returned by the query. For other aggregate functions the appropriate result for the entire result set is calculated.
To use the rollup functionality, add the WITH ROLLUP clause to the end of the query:
S.EngineerId = E.EngineerId
The results from the above query should contain the grand total for all of the rows. In the grouping column, "EngineerName", the new row has a NULL value. Sometimes this is useful and sometimes you will want to replace the NULL with more descriptive text. This process will be explained shortly.
4 June 2009