Treasury of new DB2 6.1 Features, Part 2: Grouping Sets and Super Groups

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: