Digging Out the Data

Digging out the Data

Congratulations to Rafael Mauricio Cotes Londoño, business intelligence products director for Alcuadrado S.A. in Medellín, Colombia, and Peter Borchers, database developer at Herbert Smith in London, England. Rafael won first prize of $100 for the best solution to the June Reader Challenge, "Digging out the Data." Peter won second prize of $50. Here’s a recap of the problem and the solution to the June Reader Challenge.

Problem

Mark programs a database for an Internet retailer. The database is currently hosted on a server running SQL Server 7.0, which the company will soon upgrade to SQL Server 2000. As part of his primary duties, Mark generates reports for the sales people. One of his tasks requires him to assemble the following data for the company’s 20 top-selling products, ordering the data first by totals for each product sold, then by the summary rows.

Because the Products, Categories, and Order Details tables of the Northwind sample database closely approximate his database schema, Mark decides to use them to try various approaches to retrieving the data. He also wants to avoid using temporary tables and cursors for this assignment. Help Mark write a query that generates the data he wants, yet lets him easily add other filters and parameters as needed.

Solution

To retrieve the list of 20 top-selling products by category, Mark can use the TOP clause of the SELECT statement, a feature that SQL Server 7.0 introduced. The following SELECT statement returns the data based on the Products, Categories, and Order Details tables of the Northwind database:

SELECT TOP 20 c.[CategoryName] , p.[ProductName] ,SUM( od.[Quantity] ),SUM( od.[Quantity] * od.[UnitPrice] * (1 - od.[Discount]) ) AS SoldAmountFROM [Order Details] AS odJOIN [Products] AS pON p.[ProductID] = od.[ProductID]JOIN [Categories] AS cON c.[CategoryID] = p.[CategoryID]GROUP BY c.[CategoryName], p.[ProductName]ORDER BY SoldAmount DESC

Next, Mark reads up on the CUBE and ROLLUP operators, which facilitate analytic processing. When he realizes that the ROLLUP operator will help him generate the summary totals for the top 20 products, Mark incorporates it into the following derived table:

SELECT t20.[CategoryName] , t20.[ProductName] ,SUM( t20.[Orders] ) AS TotalOrders,SUM( t20.[SoldAmount] ) AS TotalSoldAmountFROM (SELECT TOP 20 c.[CategoryName] , p.[ProductName] ,SUM( od.[Quantity] ) ,SUM( od.[Quantity] * od.[UnitPrice] *(1 - od.[Discount]) ) AS SoldAmountFROM [Order Details] AS odJOIN [Products] AS p  ON p.[ProductID] = od.[ProductID]JOIN [Categories] AS c               ON c.[CategoryID] = p.[CategoryID]GROUP BY c.[CategoryName], p.[ProductName]ORDER BY SoldAmount DESC) AS t20 (CategoryName, ProductName , Orders, SoldAmount )GROUP BY t20.[CategoryName], t20.[ProductName]WITH ROLLUP

In this way, the ROLLUP operator generates aggregates for a hierarchy of products and categories. To order the results with the required summary totals at the bottom, Mark can now use the GROUPING function in the outermost ORDER BY clause, as the following snippet of code shows:

ORDER BY GROUPING( t20.[CategoryName] ) , t20.[CategoryName],GROUPING( t20.[ProductName] ), t20.[ProductName],TotalSoldAmount DESC

In learning to use the ROLLUP operator, Mark discovers that it also helps him avoid using temporary tables and provides good performance and extensibility. He can use the ROLLUP operator to analyze the product sales by different columns. He can also compute summaries at different levels. For example, he can get sales totals by supplier for the 20 top-selling products. The following is the complete version of the SELECT statement with the required totals, ordering, and labeling of the summary rows:

USE NorthWindSELECT CASEWHEN GROUPING( t20.[CategoryName] ) = 1 THEN '** Grand Total'WHEN GROUPING( t20.[ProductName] )  = 1 THEN '* Total ' + t20.[CategoryName]ELSE t20.[CategoryName]END AS Category ,COALESCE( t20.[ProductName], ' ) AS Product ,SUM( t20.Orders ) AS TotalOrders ,SUM( t20.SoldAmount ) AS TotalSoldAmountFROM (SELECT TOP 20 c.[CategoryName], p.[ProductName] ,SUM( od.[Quantity] ) ,SUM( od.[Quantity] * od.[UnitPrice] * (1 - od.[Discount]) ) AS SoldAmountFROM [Order Details] AS odJOIN [Products] AS p  ON p.[ProductID] = od.[ProductID]JOIN [Categories] AS c  ON c.[CategoryID] = p.[CategoryID]GROUP BY c.[CategoryName], p.[ProductName]ORDER BY SoldAmount DESC) AS t20( CategoryName, ProductName, Orders, SoldAmount )GROUP BY t20.[CategoryName], t20.[ProductName]WITH ROLLUPORDER BY GROUPING( t20.[CategoryName] ) , t20.[CategoryName] ,GROUPING( t20.[ProductName] ) , t20.[ProductName] ,TotalSoldAmount

July Challenge

Now, test your SQL Server savvy in the July Reader Challenge, "Devising a View" (below). Submit your solution in an email message to [email protected] by June 21. SQL Server MVP Umachandar Jayachandran, a SQL Server Magazine technical editor, will evaluate the responses. We’ll announce the winner in an upcoming SQL Server Magazine UPDATE. The first-place winner will receive $100, and the second-place winner will receive $50.

Here’s the challenge: Ian is the database developer for several SQL Server 2000 and 7.0 installations. As part of his job managing the company’s sales database, he controls security by enforcing Sales and Manager database roles. The database holds two tables: a sales force login table called SalesUser and a sales assignment table called Items that contains a separate list for each salesperson. First, Ian created the login table with relevant columns as follows:

CREATE TABLE SalesUser (UserID int PRIMARY KEY ,UserName varchar( 128 ) NOT NULL DEFAULT ( SYSTEM_USER ) UNIQUE CLUSTERED)

Then, Ian created the sales assignment table with relevant columns as follows:

CREATE TABLE Items (ItemID int IDENTITY PRIMARY KEY,ItemDate datetime DEFAULT( CURRENT_TIMESTAMP ),Comment varchar( 255 ) NULL,UserID int REFERENCES SalesUser)

Help Ian design a view called UserItems that

GRANT ALL PRIVILEGES ON UserItems TO Manager, Sales

Corrections to this Article: