SQL Server 2008 -- Grouping Sets

1.Extends the capabilities of the GROUP BY clause for summarizing and analyzing the data.

2.Grouping Sets returns just the top-level rollup rows for each grouping level and does not include the actual group level summary information that was returned by our earlier versions of the query.

USE [tempdb] IF (object_id(N'tempdb..Inventory') IS NOT NULL)     DROP TABLE Inventory; CREATE TABLE Inventory(     Store varchar(2),     Item varchar(20),     Color varchar(10),     Quantity decimal); --INSERT DATA INSERT INTO Inventory VALUES('NY', 'Table', 'Blue', 124) INSERT INTO Inventory VALUES('NJ', 'Table', 'Blue', 100) INSERT INTO Inventory VALUES('NY', 'Table', 'Red', 29) INSERT INTO Inventory VALUES('NJ', 'Table', 'Red', 56) INSERT INTO Inventory VALUES('PA', 'Table', 'Red', 138) INSERT INTO Inventory VALUES('NY', 'Table', 'Green', 229) INSERT INTO Inventory VALUES('PA', 'Table', 'Green', 304) INSERT INTO Inventory VALUES('NY', 'Chair', 'Blue', 101) INSERT INTO Inventory VALUES('NJ', 'Chair', 'Blue', 22) INSERT INTO Inventory VALUES('NY', 'Chair', 'Red', 21) INSERT INTO Inventory VALUES('NJ', 'Chair', 'Red', 10) INSERT INTO Inventory VALUES('PA', 'Chair', 'Red', 136) INSERT INTO Inventory VALUES('NJ', 'Sofa', 'Green', 2) --BASIC GROUP BY SELECT Item, Color, SUM(Quantity) AS TotalQty, COUNT(Store) AS Stores FROM Inventory GROUP BY Item, Color ORDER BY Item, Color --Rolling Up by Level SELECT Item, Color, SUM(Quantity) AS TotalQty, COUNT(Store) AS Stores FROM Inventory GROUP BY Item, Color WITH ROLLUP --上面那一句也可換成下面這種表示方式 --GROUP BY ROLLUP(Item, Color) ORDER BY Item DESC, Color DESC --Rolling Up All Level Combinations SELECT Item, Color, SUM(Quantity) AS TotalQty, COUNT(Store) AS Stores FROM Inventory GROUP BY Item, Color WITH CUBE --上面那一句也可換成下面這種表示方式 --GROUP BY CUBE(Item, Color) ORDER BY Item DESC, Color DESC --Returning Just the Top Level(GROUPING SETS operator, new in SQL Server 2008) SELECT Store, Item, Color, SUM(Quantity) AS TotalQty FROM Inventory GROUP BY GROUPING SETS (Store, Item, Color) ORDER BY Store, Item, Color --Handling NULL Values INSERT INTO Inventory VALUES('NY', 'Lamp', NULL, 36) INSERT INTO Inventory VALUES('NJ', 'Lamp', NULL, 8) GO SELECT Store, Item, Color, SUM(Quantity) AS TotalQty FROM Inventory GROUP BY GROUPING SETS(Store), CUBE(Item, Color) ORDER BY Store DESC, Item DESC, Color DESC GO SELECT     CASE WHEN GROUPING(Store) = 1 THEN '(all)'     ELSE ISNULL(Store, '(n/a)') END AS Store,     CASE WHEN GROUPING(Item) = 1  THEN '(all)'     ELSE ISNULL(Item,  '(n/a)') END AS Item,     CASE WHEN GROUPING(Color) = 1 THEN '(all)'     ELSE ISNULL(Color, '(n/a)') END AS Color,     SUM(Quantity) AS TotalQty FROM Inventory GROUP BY GROUPING SETS(Store), CUBE(Item, Color) ORDER BY Store DESC, Item DESC, Color DESC GO --刪除TABLE DROP TABLE Inventory;