ORDERDATE	TIMESTAMP
SALESPERSONID	INTEGER
SALES_PERSON	VARGRAPHIC	102
TERRITORYID	INTEGER
TERRITORY	VARGRAPHIC	50
CUSTOMERID	INTEGER
CUSTOMER	VARGRAPHIC	50
SUBTOTAL	DECIMAL	19,4
TAXAMT	DECIMAL	19,4
FREIGHT	DECIMAL	19,4
TOTALDUE	DECIMAL	19,4

For clarification, the hierarchy of data is Customers are within a territory, and territories are within the jurisdiction of a sales person. And just for the record, I took this sample data from Microsoft's AdventureWorks SQL Server sample database and put it on DB2.

From a business intelligence (BI) standpoint, we could ask several questions about this data, such as:

1. What are the company's total sales for all orders stored in the system?
2. What is the total order value (excluding tax, freight) for each customer?
3. What is the total order value (excluding tax, freight) for each territory?
4. What is the total order value (excluding tax, freight) for each sales rep?
5. What is the total order value (excluding tax, freight) for each year?
6. What is the average order value by customer?
7. Which customers grew in sales from one year to the next?
8. Which customers dropped in sales from one year to the next?
9. What are the peak sales months of the year?
10. What is each sales rep's average order value?
11. Which territories incur the largest freight charges?
12. Which territories incur the largest sales tax collection?

Of course we could continue to ask many more questions from the data at hand.

We can easily answer the first question "What are the total sales for all orders?" using the following query:

Select Sum(SubTotal) As OrderValue From vwSalesOrderInfo

That query will return the value: 127337180.1126

We can easily answer the second question "what is the total order value (excluding tax, freight) for each customer?" using the following query:

Select Customer,Sum(SubTotal) As OrderValue
  From vwSalesOrderInfo
Group By Customer
Order By Customer

Partial formatted results for this query look like the following:

We can answer questions 3 through 5 in the same way by simply "changing out" the column to "summarize by" in the SELECT, GROUP BY, and ORDER BY clauses. For instance, instead of summarizing by Customer, we can summarize by territory.

Now, what if we wanted to combine all of this information and answer all these questions in a single query? Couldn't we simply expand the query to summarize by all combinations as follows?

  Select Year(OrderDate) As OrderYear,Sales_Person,
         Territory,Customer,Sum(SubTotal) As OrderValue
    From vwSalesOrderInfo 
Group By Year(OrderDate),Sales_Person,Territory,Customer
Order By OrderYear,Sales_Person,Territory,Customer

This modified query answered all of our questions--or did it? Actually, it didn't because the summaries given by this query offer summaries at the lowest "group" or "break" level. In this case it's still the customer column. What we're really looking for though is the ability to add additional "summary rows" in our result set that summarize the data by each of the grouping levels.

To accomplish this, we could add subtotal to the above query by using a Common Table Expression (CTE) and including some UNION ALL statements to combine the total rows sets into detail data. We'd also probably need to do a few ORDER BY tricks to ensure the subtotal rows are placed in the proper sequence within the detail rows of the result set.

This is where the super group concept comes into play. A super group will add summary rows to our query for each column or expression in the GROUP BY clause. For us old-time COBOL and RPG programmers, this is akin to "control break" totals. This new summary function is implemented by adding the WITH ROLLUP keywords to the GROUP BY clause:

  Select Year(OrderDate) As OrderYear,Sales_Person,
         Territory,Customer,Sum(SubTotal) As OrderValue
    From vwSalesOrderInfo 
Group By Year(OrderDate),Sales_Person,Territory,Customer
With Rollup  /* Implement Super Groups */
Order By OrderYear,Sales_Person,Territory,Customer

Here is a subset of the query's results:

The rows with the purple cells are summary rows added to the query results compliments of ROLLUP. Each purple cell actually contains a NULL, which is how ROLLUP behaves for the column or group of columns it is summarizing. The first summarized row tells us that the total order value for sales rep "Pamela Ansman-Wolfe" within the Northwest territory region is about $730K. The next row indicates that Ansman-Wolfe sold the same amount for all territories. (In this case, this rep has only one territory.)

If we look further down the result set we'll see similar summary rows added after each change in territory, sales person, and year. Finally, ROLLUP courteously gives us a grand total row as well. Here are the last few rows of the result set:

Again, the first row is part of the normal query and the remaining rows containing purple cells represent the "super group" rows. It's easy to follow the summarization rows as they follow the hierarchy of the GROUP BY columns.

This feature is useful, for instance, in reporting applications that require subtotal and grand total lines. And if you've ever given a user a raw data report in Excel, it's usually been up to the user to aggregate the data. Using ROLLUP in this situation will save users that extra step of adding totals themselves.

One question that may have surfaced at this point is: Can DB2 distinguish between the special "super group" summary row and a "normal" row in the result set? A first guess might be to assume rows with NULLs in a GROUP BY column are super group rows. However, testing for NULLs as a super group row will only work when it is guaranteed that the GROUP BY column values will never legitimately contain a NULL.

Fortunately, DB2 gives us a better way to test for a super group row with a new aggregate function called GROUPING. The GROUPING function accepts a single parameter containing the name of a column expression in the GROUP BY clause. The function will return a "0" if it's a normal query result row or a "1" if the row is a super group summary row (at the level of the column expression specified). For instance, if we want to know if a row is a super group at the Territory level, we can test it with the following CASE expression:

Case When Grouping(Territory)=0
   Then Territory
   Else 'Sales Person Total: ' End As Territory,

This expression will either put the Territory name in the Territory column for normal rows, or it will display the constant "Sales Person Total" in the column when it is part of a super group row.

Unfortunately (and I think this is probably a bug), the expression in the GROUP BY clause doesn't work as it should. You will find that using the following expression will return an error:

Case When Grouping(Year(OrderDate))=0
     Then Cast(Year(OrderDate) As Char(4))
     Else 'Grand Total: ' End As OrderYear,

The V6R1 SQL Reference manual implied that a GROUP BY expression is legitimate parameter for GROUPING. One way around this problem would simply be to define a view or common table expression on the base data and convert the expression Year(OrderDate) into a column of its own.

Here is a query definition that makes use of the grouping function to show "Total" constants within the super group rows instead of leaving the cells null:

With BaseData As (
  Select Year(OrderDate) As OrderYear,
         Sales_Person,
         Territory,Customer,
         Sum(SubTotal) As OrderValue,
         Grouping(Sales_Person) As SalesPersonBreak,
         Grouping(Territory) As TerritoryBreak,
         Grouping(Customer) As CustomerBreak
    From vwSalesOrderInfo 
Group By Year(OrderDate),Sales_Person,Territory,Customer
With Rollup
Order By OrderYear,Sales_Person,Territory,Customer)
Select OrderYear,
       Case When SalesPersonBreak=0
            Then Sales_Person
            Else 'Year Total'  As Sales_Person,
       Case When TerritoryBreak=0
            Then Territory
            Else 'Sales Rep Total' As Territory,
       Case When CustomerBreak=0
            Then Customer
            Else 'Territory Total' As Customer,
       OrderValue
  From BaseData

A common table expression (CTE) is required in this case because we need the CTE to sort the base data appropriately and then insert the summary rows appropriately into the sorted data. Otherwise, the constant we specify within our GROUP BY columns would potentially make the ORDER BY clause sort the rows incorrectly.

The CUBE Function

A second type of super group involves the concept of the CUBE, which is like ROLLUP on steroids. Whereas ROLLUP creates a summary row for each break level (from lowest order to highest), CUBE creates several summary rows for the various combinations of distinct values within the columns of the GROUP BY clause.

Continuing with our previous example, say we specify WITH CUBE in the GROUP BY clause instead of WITH ROLLUP:

  Select Year(OrderDate) As OrderYear,Sales_Person,
         Territory,Customer,Sum(SubTotal) As OrderValue
    From vwSalesOrderInfo 
Group By Year(OrderDate),Sales_Person,Territory,Customer
With Cube /* Implement Super Groups */
Order By OrderYear,Sales_Person,Territory,Customer

In addition to the normal rows produced by the query, shown below is a sample of the summary rows created by the WITH CUBE option:

As you can see, this section of results shows summary combinations for year, sales rep, and customer followed by year, territory and customer.

Scanning further down the result set's summary rows, here is another sample:

Now we're getting summary combinations for year, territory, and customer, trailed by rows that summarize by sales rep, territory, and customer. If you were to comb through all the rows, you'd see rows showing totals by year alone, rows by customer alone, and all variations in between!

If you're familiar with pivot tables in Excel or Access, then you'll see a familiar connection. Pivot tables, which are often associated with BI functionality, are powerful because they allow the user to summarize a base set of data by any combination of supplied summary values. CUBE does something similar for a DB2 query in that it summarizes for various combinations of data within the data set.

When I look at the base sample query I wrote to demonstrate the WITH ROLLUP option, I'm now questioning the decision to make YEAR the highest order column in the GROUP BY. Perhaps it would be more useful to see the sales person as the highest level summarization and then have sales year as the next summary level within sales person. Should I rework the query? With CUBE, it doesn't really matter because CUBE will return all of these summarizations. In fact, CUBE quickly allows a user to answer many of the questions asked at the beginning of this tip because CUBE gives the answer to all combinations of summarizations that are possible within the query.

Be careful though as the number of summary rows returned by CUBE can be very large. In my test query shown above, there are 1,125 "normal" rows returned by the query. The CUBE function that returns a summary row for all variations of distinct values in the GROUP BY columns ended up adding 5,435 rows to the result set! So CUBE has given me all variations of possible summary rows; all that is left is a matter of locating which summary row(s) to review.

Grouping Sets

Often in BI applications, the big picture is more important than the details. In these cases, it's more useful to study the forest instead of the individual trees. CUBE and ROLLUP give us detail information (trees) along with some summary information (helping us see the forest), but sometimes this is too much information. That's where GROUPING SET comes in.

When GROUPING SET is specified on the GROUP BY clause, the query will only return "aggregates" (i.e., the big picture). Each "set" to be aggregated is specified by enclosing a column list by parenthesis. Take a look at the following query:

Select Territory,Sales_Person,
       Sum(SubTotal) As OrderValue,
       Avg(SubTotal) As AvgOrder
  From vwSalesOrderInfo
  Where Sales_Person<>'* House Account *'
    And Territory Not In ('Australia',
	'France','Germany','United Kingdom')
Group By Grouping Sets ((Territory,Sales_Person))
Order By Territory,Sales_Person

This query returns this aggregated result set for combinations of territory and sales_person:

A similar query with a slightly different GROUPING SET, looks like this:

Select Territory, Sales_Person,
       Sum(SubTotal) As OrderValue,
       Avg(SubTotal) As AvgOrder
  From vwSalesOrderInfo
  Where Sales_Person<>'* House Account *'
    And Territory Not In ('Australia',
	'France','Germany','United Kingdom')
Group By Grouping Sets ((Territory),(Sales_Person))
Order By Territory,Sales_Person

This query actually has two distinct GROUPING SETS, one for territory and one for sales_person. The query returns this result set:

Whereas in the prior example, we had a single GROUPING SET consisting of two columns giving a summary for each combination, by separating each column into its own "set," each column now gets its own unique group total.

Additionally, we actually have the option of applying a CUBE or ROLLUP operation to any or all of our GROUPING SETS:

Select Territory,Sales_Person,
       Sum(SubTotal) As OrderValue,
       Avg(SubTotal) As AvgOrder
  From vwSalesOrderInfo
 Where Sales_Person<>'* House Account *'
   And Territory Not In ('Australia',
   'France','Germany','United Kingdom')
Group By Grouping Sets (
Rollup(Territory,Sales_Person))
Order By Territory,Sales_Person

As we've already seen, ROLLUP will add subtotals and a grand total to the result set:

Summary (Or Should I Say Aggregate?)

These super group and grouping set functions are aimed at BI applications. They allow a developer to provide many answers from a data set all within the confines of a single, simple query. As always, remember to review your indexes and optimizer messages to make sure heavy duty queries will be acceptable before moving them into production. Once your user community realizes the power to easily analyze their data has been unleashed, writing these BI queries will be all the rage within your company.

Michael Sansoterra is a programmer/analyst for i3 Business Solutions, an IT services firm based in Grand Rapids, Michigan. Send your questions or comments for Mike to Ted Holt via the IT Jungle Contact page.

RELATED STORY

Treasury Of New DB2 6.1 Features, Part 1: Query Enhancements

---

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot