Summarizing Data with
By Dave Long
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
in a DataGrid.
WITH ROLLUPsyntax, as we'll see shortly, can only be used when a SQL query contains a
GROUP BYclause. The
GROUP BYclause 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:
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
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
ROLLUP operator as follows:
What this means is that
ROLLUPgenerates a result set showing aggregates for a hierarchy of values in the selected columns.
ROLLUPoutputs each individual row with the corresponding summary information (this may be
MIN, or whatever you specify) as the last row in each grouped category. Lets take a look at how we would add the
ROLLUPoperator to the SQL query from above:
First draw you attention to the
GROUP BY clause. Note that since we have to non-aggregate
fields in our
SELECT clause (
the results will be partitioned by category and products. Also note that we use the
aggregate function. This will cause this query to display the sum of the
UnitsInStock fields by distinct products and categories.
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,
WITH ROLLUP will cause summary data to be shown for each unique category as well as for
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
Meat/Poultry category, it will display the sum of the
UnitsInStock fields in a summary row, having a
NULL value for the
CASE ... WHEN ... THEN ... ELSE ... END syntax tells the SQL engine that when it
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
|Meat/Poultry||Mishi Kobe Niku||97.0000||29|
|Produce||Manjimup Dried Apples||53.0000||20|
|Produce||Uncle Bob's Organic Dried Pears||30.0000||15|
|Seafood||Boston Crab Meat||18.4000||123|
|Seafood||Escargots de Bourgogne||13.2500||62|
|Seafood||Jack's New England Clam Chowder||9.6500||85|
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.