The addition of the COMPUTE and COMPUTE BY columns was simple. However, I skipped a crucial requirement for using COMPUTE BY: You must supply an ORDER BY clause before the COMPUTE BY clause. For COMPUTE BY to work, the data must be ordered in such a way that the aggregate has a logical placement within the result set. For example, in Listing 5, you want to see the sum of quantity for each ProductID. To have a place to list the sum, you need to organize the rows so that all orders for a given ProductID appear together. By ordering the data, the ORDER BY logically organizes the data into groups. In the result set for Listing 5, the summary data appears physically with each grouping after each new ProductID (a sum appears after each ProductID’s details). To provide this logical ordering for COMPUTE BY, SQL Server requires the ORDER BY clause. In Listing 5, the only aggregates that you can add to the COMPUTE BY clause are those that include ProductID. Listing 6 shows the syntax for adding the average quantity ordered and the count of orders for each Product ID. If you want additional groupings and aggregations, you also need a more complex ORDER BY clause.
To produce summary data for a variety of groups, you need to state multiple attributes in the ORDER BY clause. For example, you might want a broader sum of items sold by product type (e.g., the sum of all products that have the same CategoryName) in addition to the sum of each product (by ProductID). To do this, you need to logically order the data to create groupings by category so that you can place the sum after the entire group. You could order all the data by Product and then Category or by Category and then ProductID. Both are acceptable syntactically, but the latter makes more logical sense because you’re really interested in the total sum of all products within each category and then each product individually. Listing 7 shows the changes necessary to create this COMPUTE BY statement.
Listing 7 uses multiple COMPUTE and COMPUTE BY clauses. With this query's ORDER BY clause, you can perform computations only in left-based subsets of the ORDER BY clause. The COMPUTE and COMPUTE BY clauses SQL Server supports for an ORDER BY CategoryID, ProductID statement are:
COMPUTE aggregate(quantity) BY CategoryID, ProductID
COMPUTE aggregate(quantity) BY CategoryID
COMPUTE aggregate(quantity) — for a grand total of quantity
These are left-based because they contain a subset of the ORDER BY starting from the left. Not only are all three of these supported, but all three appear in Listing 7. Listing 7's result set returns detailed data about an order (the OrderDate, CategoryName, ProductName, CompanyName, and Quantity ordered) as well as summary data about Categories and Products. It does so by using all three COMPUTE clauses possible for this ORDER BY clause.
Using text mode, review the output of this data. Next, run the query a second time using grid mode (Query, Results in Grid or Ctrl-D) to see how the results look different. In grid mode, SQL Server returns each of the aggregates in a separate grid from the details and lists them together horizontally (when you request multiple aggregates). In text mode, Query Analyzer returns the summary data vertically in the aggregate column. Grid mode collapses the data better if you’re quickly looking for the sums, but I find the sums harder to read horizontally, especially if I’m asking for multiple aggregates from multiple columns. Additionally, in both grid mode and text mode, SQL Server defines the column headers—for example, sum for sum(), cnt for count(), avg for avg()—and you can't change them. If you plan to handle the COMPUTE or COMPUTE BY result set in your own custom client application, you should work with the application thoroughly to understand how it returns this data. Some client APIs make retrieving values from nontabular result sets difficult.
In reviewing the result set from Listing 7’s code, you notice that some customers purchase the same products multiple times. You realize that what you’re really interested in is the sum of the items purchased by each customer (not individual orders by each customer), but you want to see the totals ordered by the combination of CategoryName and ProductName and by just CategoryName alone. This may seem complex, but it’s not really. You can easily combine GROUP BY with COMPUTE and COMPUTE BY. Group all the individual customer orders for a given product (removing the date of purchase), then list them with each category and product listing.
Logically, the GROUP BY clause should come before the ORDER BY and COMPUTE clauses; GROUP BY will require an aggregate in the SELECT list (to contain the sum of products a customer purchased). Finally, the COMPUTE clauses will produce an aggregate of the individual aggregates. Listing 8 shows the result of adding the GROUP BY clause to group the customer purchases. Note that the COMPUTE clauses must use SUM to gather the sums of all groups. In fact, you must use the complete expression SUM(SUM(OD.Quantity)) and not SUM('Total Purchased') because using column headers in the COMPUTE clause isn't allowed. Column headers (sometimes called aliases) are allowed only in the ORDER BY clause.
Finally, if you want date information, you can include the date in the SELECT list, as long as it’s an aggregated date such as the min or max date. Adding aggregates for date can be helpful in determining the earliest order (min(OrderDate)) or the most recent order (max(OrderDate)). Listing 9 shows a query that takes advantage of this information, showing the count of purchases, the first purchase date, and the most recent purchase date for each CompanyName.
Although COMPUTE and COMPUTE BY might appear challenging to use at first because they create multiple result sets, I recommend using them with the summary data, where other aggregates fail to provide all the necessary information. Overall, COMPUTE and COMPUTE BY are an excellent means of reporting when detailed printed reports are necessary.
Next time: Back to views… Can you modify data through a view?
End of Article
Prev. page
1
[2]
next page -->
