Summarizing Data with ROLLUP

By Dave Long

---

Introduction

Oftentimes, when building Web-based reporting tools we need to show totals and sub-totals for information stored in the database. For example, imagine that you worked at an eCommerce site like Amazon.com that sold products from a variety of categories: books, electronics, clothes, etc. Managers would likely be very interested in seeing a summary of sales for a given time period, showing sales from each item in each category, as well as a summary listing the total number of sales for each category and for all products.

A common way of providing such summarized data is the running total method. This approach uses a variable, call it myTotal, which is incremented for each record that is to be summarized. For example, in the eCommerce example, this variable would contain, the total number of sales across all product categories. Therefore, while iterating through the product sales records, we'd add each record's sales to this variable. If you need to display the sum of a column in a DataGrid, for example, this method works well. In fact, there is an article here on 4Guys, Displaying a Column's Sum in the Footer.

In this article, however, we will examine one alternative to the running total method: the WITH ROLLUP method. WITH ROLLUP is syntax that can be used in a SQL query to provide summarized information directly in the database resultset. By the end of this article we will have examined the results produced by a SQL query using WITH ROLLUP, as well as how to display the results of a WITH ROLLUP in a DataGrid.

Understanding WITH ROLLUP

The WITH ROLLUP syntax, as we'll see shortly, can only be used when a SQL query contains a GROUP BY clause. The GROUP BY clause provides a means to return aggregated data. To demonstrate this, let's look at the products and product categories in the Northwind database. In order to return the sum of prices for a category, and the sum of Units in Stock for the same category, we would write SQL to the effect of:

SELECT CategoryName, SUM(UnitPrice) as UnitPrice, SUM(UnitsinStock) as UnitsinStock FROM Products INNER JOIN Categories On Products.CategoryID = Categories.CategoryID GROUP BY CategoryName

This would return results like:

Category		UnitPrice		UnitsinStock
Beverages		455.75			559
Condiments		276.75			507
Confections		327.08			386

These results show the sum of prices for each product and the sum of Units in Stock for each product that exists in the category the product belongs to. If you are unfamiliar with the GROUP BY clause, I recommend that you first read Scott Mitchell's article Using the GROUP BY Clause.

In examining the results of the above SQL query, your first instinct may be to say, "That's exactly what I need for displaying summarized data." While the above SQL query does generate interesting, summarized data, realize that it does not cut the mustard if you want to see not only the summarized totals, but also the price and Units in Stock for each of the actual products themselves. This is where the ROLLUP operator becomes useful.

The SQL Books Online describes the ROLLUP operator as follows:

ROLLUP generates a result set showing aggregates for a hierarchy of values in the selected columns.

What this means is that ROLLUP outputs each individual row with the corresponding summary information (this may be SUM, COUNT, AVG, MAX, MIN, or whatever you specify) as the last row in each grouped category. Lets take a look at how we would add the ROLLUP operator to the SQL query from above:

SELECT CASE WHEN (Grouping(CategoryName)=1) THEN 'MainTotal' ELSE CategoryName END AS CategoryName, CASE WHEN (Grouping(ProductName)=1) THEN 'SubTotal' ELSE Productname END AS ProductName, Sum(UnitPrice) as UnitPrice, Sum(UnitsinStock) as UnitsInStock FROM Products INNER JOIN Categories On Products.CategoryID = Categories.CategoryID GROUP BY CategoryName, ProductName WITH ROLLUP

First draw you attention to the GROUP BY clause. Note that since we have to non-aggregate fields in our SELECT clause (CategoryName and ProductName), the results will be partitioned by category and products. Also note that we use the SUM aggregate function. This will cause this query to display the sum of the UnitPrice and UnitsInStock fields by distinct products and categories.

The WITH ROLLUP in the GROUP BY clause informs the SQL engine that rather than displaying just the summation, that each row for each category and product should be displayed. Furthermore, the WITH ROLLUP will cause summary data to be shown for each unique category as well as for all categories.

When generating summary rows, the SQL engine will insert a NULL value for the field to which the summarizing is being performed across. That is, when the WITH ROLLUP completes the grouping of the Meat/Poultry category, it will display the sum of the UnitPrice and UnitsInStock fields in a summary row, having a NULL value for the ProductName. The CASE ... WHEN ... THEN ... ELSE ... END syntax tells the SQL engine that when it encounters such NULL values due to the ROLLUP, a suitable textual output is displayed (namely Maintotal or Subtotal).

The results of this ROLLUP query are shown below. The rows displaying summary information have been highlighted to make it easy to see the summarizing effect of ROLLUP.

CategoryName	ProductName	unitprice	UnitsinStock
Meat/Poultry	Alice Mutton	39.0000	0
Meat/Poultry	Mishi Kobe Niku	97.0000	29
Meat/Poultry	Pbti chinois	24.0000	115
Meat/Poultry	Perth Pasties	32.8000	0
Meat/Poultry	Thringer Rostbratwurst	123.7900	0
Meat/Poultry	Tourtihre	7.4500	21
Meat/Poultry	SubTotal	324.0400	165
Produce	Longlife Tofu	10.0000	4
Produce	Manjimup Dried Apples	53.0000	20
Produce	Rvssle Sauerkraut	45.6000	26
Produce	Tofu	23.2500	35
Produce	Uncle Bob's Organic Dried Pears	30.0000	15
Produce	SubTotal	161.8500	100
Seafood	Boston Crab Meat	18.4000	123
Seafood	Carnarvon Tigers	62.5000	42
Seafood	Escargots de Bourgogne	13.2500	62
Seafood	Gravad lax	26.0000	11
Seafood	Ikura	31.0000	31
Seafood	Inlagd Sill	19.0000	112
Seafood	Jack's New England Clam Chowder	9.6500	85
Seafood	Konbu	6.0000	24
Seafood	Nord-Ost Matjeshering	25.8900	10
Seafood	Rvd Kaviar	15.0000	101
Seafood	Rogede sild	9.5000	5
Seafood	Spegesild	12.0000	95
Seafood	SubTotal	248.1900	701
MainTotal	SubTotal	2222.7100	3119

And that's all there is to it! In Part 2 we will examine how to display the results of a ROLLUP query in an ASP.NET DataGrid.

Read Part 2!