Execute the following Microsoft SQL Server T-SQL scripts in Management Studio Query Editor to create "Total" labels at different grouping levels by applying the GROUPING function.

-- SQL Server grouping function - SQL group by - SQL total - SQL CASE function

-- SQL SUM aggregate - WITH CUBE aggregates

USE AdventureWorks;

SELECT   TotalProduct = CASE GROUPING(ProductModelID)

                          WHEN 0 THEN ''

                          WHEN 1 THEN 'Total'

                          ELSE ''

                        END,

         TotalModel = CASE GROUPING(p.Name)

                        WHEN 0 THEN ''

                        WHEN 1 THEN 'Total'

                        ELSE ''

                      END,

         Model = isnull(convert(VARCHAR,ProductModelID),''),

         ProductName = isnull(p.Name,''),

         OrderQuantity = SUM(OrderQty)

FROM     Production.Product p

         INNER JOIN Sales.SalesOrderDetail sod

           ON p.ProductID = sod.ProductID

GROUP BY ProductModelID,

         p.Name WITH CUBE

ORDER BY TotalModel,

         TotalProduct;

GO

/* Partial results

TotalProduct      TotalModel  Model ProductName                   OrderQuantity

Total                               Touring-3000 Yellow, 62             844

Total                               Water Bottle - 30 oz.               6815

Total                               Women's Mountain Shorts, L          3244

Total                               Women's Mountain Shorts, M          1139

Total                               Women's Mountain Shorts, S          3296

Total                               Women's Tights, L                   2123

Total                               Women's Tights, M                   394

Total                               Women's Tights, S                   2072

                  Total       86                                        1160

                  Total       85                                        862

                  Total       84                                        134

*/