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 apply the GROUPING function?

Execute the following Microsoft SQL Server T-SQL script in SSMS Query Editor to distinguish the rollup line from detail lines by using the GROUPING function:

-- T-SQL GROUPING function - GROUP BY ROLLUP

USE AdventureWorks;

 

SELECT   Staff = CASE

                   WHEN GROUPING(c.LastName + ', ' + c.FirstName) = 1

                     THEN 'Rollup'

                   ELSE c.LastName + ', ' + c.FirstName

                 END,

         SalesCount = count(* )

FROM     Sales.SalesOrderHeader soh

         INNER JOIN HumanResources.Employee e

           ON e.EmployeeID = soh.SalesPersonID

         INNER JOIN Person.Contact c

           ON e.ContactID = c.ContactID

GROUP BY c.LastName + ', ' + c.FirstName WITH ROLLUP

ORDER BY c.LastName + ', ' + c.FirstName

/*

Staff                   SalesCount

Rollup                  3806

Abbas, Syed             16

Alberts, Amy            39

Ansman-Wolfe, Pamela    95

Blythe, Michael         450

Campbell, David         189

Carson, Jillian         473

Ito, Shu                242

Jiang, Stephen          48

Mensa-Annan, Tete       140

Mitchell, Linda         418

Pak, Jae                348

Reiter, Tsvi            429

Saraiva, José           271

Tsoflias, Lynn          109

Valdez, Rachel          130

Vargas, Garrett         234

Varkey Chudukatil       175

*/

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