GROUP BY groups the result set into summary rows by provided columns. For example, consider below data which contains sales figures by region.
Group Name SalesYTD
—————— ——————- —————
North America Northwest 123237.00
North America Northwest 37534.00
North America Northwest 48003.00
North America Southwest 164232.00
North America Southeast 39667.00
North America Southeast 105810.00
Europe France 74569.00
Europe Germany 59456.00
Pacific Australia 93403.00
Europe United Kingdom 78327.00
This data can be summarized using a GROUP BY clause as below:
– © 2011 – Vishal (http://SqlAndMe.com)
SELECT [Group], [Name], SUM([SalesYTD]) AS 'Total Sales'
FROM #TempTable
GROUP BY [Group], [Name]
ORDER BY [Group], [Name]
Result Set:
Group Name Total Sales
—————— —————— —————–
Europe France 74569.00
Europe Germany 59456.00
Europe United Kingdom 78327.00
North America Northwest 208774.00
North America Southeast 145477.00
North America Southwest 164232.00
Pacific Australia 93403.00
(7 row(s) affected)
In the above result we can see that the data is summarized by [Group] and [Name].
WITH ROLLUP:
ROLLUP can be used to generate a subtotal rows and a grand total row for aggregate rows.
SELECT [Group], [Name], SUM([SalesYTD]) AS 'Total Sales'
FROM #TempTable
GROUP BY [Group], [Name] WITH ROLLUP
Result Set:
Group Name Total Sales
——————- ——————- ———————
Europe France 74569.00
Europe Germany 59456.00
Europe United Kingdom 78327.00
Europe NULL 212352.00
North America Northwest 208774.00
North America Southeast 145477.00
North America Southwest 164232.00
North America NULL 518483.00
Pacific Australia 93403.00
Pacific NULL 93403.00
NULL NULL 824238.00
(11 row(s) affected)
As we can see in the result set, 4 rows have been added, 3 for sub total of each [Group], and 1 row for grand total.
GROUPING():
GROUPING() function can be used to check whether the row is aggregated or not. It returns 1 for aggregated rows.
– © 2011 – Vishal (http://SqlAndMe.com)
SELECT [Group], [Name], SUM([SalesYTD]) AS 'Total Sales',
GROUPING([Group]) AS 'Aggregated',
GROUPING([Name]) AS 'Aggregated'
FROM #TempTable
GROUP BY [Group], [Name] WITH ROLLUP
Result Set:
Group Name Total Sales Aggregated Aggregated
——————- ——————– ————- ———— ————
Europe France 74569.00 0 0
Europe Germany 59456.00 0 0
Europe United Kingdom 78327.00 0 0
Europe NULL 212352.00 0 1
North America Northwest 208774.00 0 0
North America Southeast 145477.00 0 0
North America Southwest 164232.00 0 0
North America NULL 518483.00 0 1
Pacific Australia 93403.00 0 0
Pacific NULL 93403.00 0 1
NULL NULL 824238.00 1 1
(11 row(s) affected)
Finally, we can use GROUPING() function to identify and replace the 'NULL' in aggregated rows to something meaningful. for example, 'ALL'!!!!.
SELECT CASE GROUPING([Group])
WHEN 1 THEN 'ALL'
ELSE [Group] END AS 'Group',
CASE GROUPING([Name])
WHEN 1 THEN 'ALL'
ELSE [Name] END AS 'Name',
SUM([SalesYTD]) AS 'Total Sales'
FROM #TempTable
GROUP BY [Group], [Name] WITH ROLLUP
Result Set:
Group Name Total Sales
—————— ——————- ——————–
Europe France 74569.00
Europe Germany 59456.00
Europe United Kingdom 78327.00
Europe ALL 212352.00
North America Northwest 208774.00
North America Southeast 145477.00
North America Southwest 164232.00
North America ALL 518483.00
Pacific Australia 93403.00
Pacific ALL 93403.00
ALL ALL 824238.00
(11 row(s) affected)
Hope This Helps!
扫一扫
6002
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
