Computing Totals and Subtotals


You can use other aggregate functions to obtain summarizations and totals of data in your tables.

Tip

All the modifiers you've learned to use for the COUNT function are available for any other aggregate function.


Computing Totals

You have to obtain totals to answer questions like: "How much money did we get from sales?" or "How many units have we sold of this product?" You could count records using a custom application. However, you will be able to get the desired information more efficiently by directly querying the database.

Using SUM

The SUM function does exactly what you would expect: it yields the sum of values in a column. The values have to be of numeric datatypes. In addition, the function will yield an error if it encounters a NULL value when trying to calculate a total. Let's examine the ways in which you can use the SUM function to give a variety of useful information. (The scripts in this section are included in the samples as SumExamplesFromText.sql in the \SqlScripts folder.)

Generating Totals

1.

To total the LineTotal column from the SalesOrderDetail table in the AdventureWorks database, enter and execute the following script:

SELECT SUM(LineTotal) AS [Grand Total] FROM Sales.SalesOrderDetail


2.

Make the result given by this script more useful by displaying total sales by product by executing the following script:

SELECT ProductID, SUM(LineTotal) AS [Product Total] FROM Sales.SalesOrderDetail GROUP BY ProductID


3.

This result is better, but the ProductID might not be meaningful enough for users. Improve this query by joining with the product table to display the product names as opposed to the product IDs using the following query:

SELECT Production.Product.Name, SUM(Sales.SalesOrderDetail.LineTotal) AS [Product Total]     FROM Sales.SalesOrderDetail INNER JOIN Production.Product     ON Sales.SalesOrderDetail.ProductID = Production.Product.ProductID GROUP BY Production.Product.Name ORDER BY Production.Product.Name


4.

Now your results are much more useful to the user. Perhaps we could give the user some information about the Category and the SubCategory of each Product. To do so, run the following script:

SELECT C.Name AS Category,     S.Name AS SubCategory,     P.Name AS Product, SUM(O.LineTotal) AS [Product Total] FROM Sales.SalesOrderDetail AS O     INNER JOIN         Production.Product AS P ON O.ProductID = P.ProductID     INNER JOIN         Production.ProductSubcategory AS S ON P.ProductSubcategoryID = S.ProductSubcategoryID     INNER JOIN         Production.ProductCategory AS C ON S.ProductCategoryID = C.ProductCategoryID GROUP BY P.Name, C.Name, S.Name ORDER BY Category, SubCategory, Product


We obtain these results:

Table 5-3. Results

Category

SubCategory

Product

Sales

Accessories

Bike Racks

Hitch Rack - 4-Bike

237096.16

Accessories

Bike Stands

All-Purpose Bike Stand

39591.00

Accessories

Bottles and Cages

Mountain Bottle Cage

20229.75

Accessories

Bottles and Cages

Road Bottle Cage

15390.88

Accessories

Bottles and Cages

Water Bottle - 30 oz.

28654.16

Accessories

Cleaners

Bike Wash - Dissolver

18406.97

Accessories

Fenders

Fender Set - Mountain

46619.58

Accessories

Helmets

Sport-100 Helmet, Black

16.869.52

...

...

...

...


Now, we are giving the user a very useful set of information.

Having too many items could make it difficult to analyze the results. In addition, it is possible that the users might need a summary on the SubCategory and Category fields. You can use the ROLLUP function to accomplish this.

Using ROLLUP to Compute Subtotals

T-SQL provides some operators for the GROUP BY clause that allow you to get information not only in detail, but also as a summary of each of the fields you define as arguments for the GROUP BY clause. (The scripts in this section are included in the samples as RollupExamplesFromText.sql in the \SqlScripts folder.)

Generating Subtotals

1.

Alter the preceding SELECT statement to use only Category and SubCategory information. This will decrease the number of rows returned and make it a little easier to understand the data. We'll also add the WITH ROLLUP operator to the GROUP BY clause to see subtotals for both Category and SubCategory:

SELECT C.Name AS Category,     S.Name AS SubCategory,     SUM(O.LineTotal) AS Sales FROM Sales.SalesOrderDetail AS O     INNER JOIN Production.Product AS P         ON O.ProductID = P.ProductID         INNER JOIN Production.ProductSubcategory AS S         ON P.ProductSubcategoryID = S.ProductSubcategoryID         INNER JOIN Production.ProductCategory AS C         ON S.ProductCategoryID = C.ProductCategoryID GROUP BY C.Name, S.Name WITH ROLLUP ORDER BY Category, SubCategory


Table 5-4. Results

Category

Subcategory

Sales

NULL

NULL

$ 109846381.40

Accessories

NULL

1272072.88

Accessories

Bike Racks

237096.16

Accessories

Bike Stands

39591.00

Accessories

Bottles and Cages

64274.79

Accessories

Cleaners

18406.97

Accessories

Fenders

46619.58

Accessories

Helmets

484048.53

Accessories

Hydration Packs

105826.42

Accessories

Locks

16240.22

Accessories

Pumps

13514.69

Accessories

Tires and Tubes

246454.53

Bikes

NULL

94651172.70

Bikes

Mountain Bikes

36445443.94

...

...

...


You can see from this result set that the overall total for sales is $109,846,381.40, the total sales for the Accessories Category is $1,272,072.88, the total sales for the Bike Racks SubCategory is $237,096.16, and so on. However, displaying the totals in this fashion is not the best way to present information.

2.

To get more usefully structured result sets, you can use a special aggregate function called GROUPING. This function helps distinguish between total rows and detail rows. Add the GROUPING function to your script for the Category and the SubCategory fields so that it will be easy to see the total rows in your result set by running the following script:

SELECT     C.Name AS Category,     S.Name AS SubCategory,     SUM(O.LineTotal) AS Sales,     GROUPING(C.Name) AS IsCategoryGroup,     GROUPING(S.Name) AS IsSubCategoryGroup FROM         Sales.SalesOrderDetail AS O     INNER JOIN Production.Product AS P         ON O.ProductID = P.ProductID     INNER JOIN Production.ProductSubcategory AS S         ON P.ProductSubcategoryID = S.ProductSubcategoryID     INNER JOIN Production.ProductCategory AS C         ON S.ProductCategoryID = C.ProductCategoryID GROUP BY C.Name, S.Name WITH ROLLUP ORDER BY Category, SubCategory


3.

Now change the order of the rows in the result set by ordering by these grouping values. Doing so displays the information in a more appropriate style for a report.

SELECT     C.Name AS Category,     S.Name AS SubCategory,     SUM(O.LineTotal) AS Sales,     GROUPING(C.Name) AS IsCategoryGroup,     GROUPING (S.Name) AS IsSubCategoryGroup FROM Sales.SalesOrderDetail AS O     INNER JOIN Production.Product AS P         ON O.ProductID = P.ProductID     INNER JOIN Production.ProductSubcategory AS S         ON P.ProductSubcategoryID = S.ProductSubcategoryID     INNER JOIN Production.ProductCategory AS C         ON S.ProductCategoryID = C.ProductCategoryID GROUP BY C.Name, S.Name WITH ROLLUP ORDER BY     IsCategoryGroup,     Category,     IsSubCategoryGroup,     SubCategory


The result set for the above script is shown below. Note that some rows have been omitted to allow presentation within this text:

Table 5-5. Results

Category

SubCategory

Sales

 

IsCategoryGroup

IsSubCategoryGroup

Accessories

Bike Racks

$ 237096.16

 

0

0

Accessories

Bike Stands

39591.00

 

0

0

Accessories

NULL

1272072.88

 

0

1

Bikes

Mountain Bikes

36445443.94

 

0

0

Bikes

Touring Bikes

$ 14296291.26

 

0

0

Bikes

NULL

94651172.70

 

0

1

Clothing

Bib-Shorts

167558.62

 

0

0

Clothing

Caps

51229.45

 

0

0

Clothing

NULL

2120542.52

 

0

1

Components

Bottom Brackets

51826.37

 

0

0

Components

Wheels

680831.35

 

0

0

Components

NULL

11802593.29

 

0

1

...

...

...

 

...

...

NULL

NULL

109846381.40

 

1

1


4.

Finally, you can change the display of the NULL value to something more appropriate by using a CASE statement in the query:

SELECT     CASE GROUPING(C.Name)         WHEN 1 THEN 'Category Total'         ELSE C.Name     END AS Category,     CASE GROUPING(S.Name)         WHEN 1 THEN 'Sub-category Total'         ELSE S.Name     END AS SubCategory,     SUM(O.LineTotal) AS Sales,     GROUPING(C.Name) AS IsCategoryGroup,     GROUPING(S.Name) AS IsSubCategoryGroup FROM Sales.SalesOrderDetail AS O     INNER JOIN Production.Product AS P         ON O.ProductID = P.ProductID     INNER JOIN Production.ProductSubcategory AS S         ON P.ProductSubcategoryID = S.ProductSubcategoryID     INNER JOIN Production.ProductCategory AS C         ON S.ProductCategoryID = C.ProductCategoryID GROUP BY C.Name, S.Name WITH ROLLUP ORDER BY     IsCategoryGroup,     Category,     IsSubCategoryGroup,     SubCategory


You can decide whether to show the GROUPING values by including or excluding them from the SELECT clause of your script. Hidden GROUPING values can be used within the script in other clauses, such as the ORDER BY clause. If you obtain GROUPING values in your application, you can use them to add formatting for the total rows in reports or on screens.




Solid Quality Learning - Microsoft SQL Server 2005. Applied Techniques Step by Step
Microsoft SQL Server 2005: Applied Techniques Step by Step (Pro-Step by Step Developer)
ISBN: 0735623163
EAN: 2147483647
Year: 2006
Pages: 115

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net