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
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.
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.
