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 VIDEOS
 
 
SQL E/BOOKS   WORLD, USA & SQL NEWS   FORMAT
SCRIPTS SQL 2005 SQL 2008 ARTICLES
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
SQL 2016 DESIGN & PROGRAMMING
 
 
 
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