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. ResultsCategory | 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. ResultsCategory | 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. ResultsCategory | 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. |