DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to use the GROUPING function?

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

*/

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE