When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
ASPFAQs.com
Message Board
Related Web Technologies
User Tips!
Coding Tips
Search

Sections:
Book Reviews
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
Web Hosts
XML
Information:
Advertise
Feedback
Author an Article

ASP ASP.NET ASP FAQs Message Board Feedback
 
Print this Page!
Published: Wednesday, July 30, 2003

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.

- continued -

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.

CategoryNameProductNameunitpriceUnitsinStock
Meat/PoultryAlice Mutton39.00000
Meat/PoultryMishi Kobe Niku 97.0000 29
Meat/Poultry Pbti chinois 24.0000115
Meat/PoultryPerth Pasties32.80000
Meat/PoultryThringer Rostbratwurst123.79000
Meat/PoultryTourtihre7.450021
Meat/PoultrySubTotal324.0400165
ProduceLonglife Tofu10.00004
ProduceManjimup Dried Apples53.000020
ProduceRvssle Sauerkraut45.600026
ProduceTofu23.250035
ProduceUncle Bob's Organic Dried Pears30.000015
ProduceSubTotal161.8500100
SeafoodBoston Crab Meat18.4000123
SeafoodCarnarvon Tigers62.500042
SeafoodEscargots de Bourgogne13.250062
SeafoodGravad lax26.000011
SeafoodIkura31.000031
SeafoodInlagd Sill19.0000112
SeafoodJack's New England Clam Chowder9.650085
SeafoodKonbu6.000024
SeafoodNord-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 SubTotal2222.71003119

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!



  • ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article