SQL Server – TSQL – GROUP BY WITH ROLLUP
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!
Vishal
If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe
Explained here much better than the 70-433 book I am also using so thought I’d just say thanks.
Just curious, is there any advantage of this syntax over the more commonly supported ROLLUP() function (DB2, Oracle, Sybase SQL Anywhere, SQL Server)?
GROUP BY [Group], [Name] WITH ROLLUP
– or
GROUP BY ROLLUP([Group], [Name])
GROUP BY ROLLUP([Group], [Name]) should be used in development work. WITH ROLLUP and WITH CUBE are non-ISO compliant syntax and will be removed in a future version. More info here: http://technet.microsoft.com/en-us/library/ms177673.aspx
Thanks for the clarification. That’s what I thought. I was confused, though, because MySQL also has a WITH ROLLUP clause, but doesn’t support the ROLLUP(…) function…
I have a sales order table that’s joined to an account table. The requirement is to show all Accounts where the value of their sales orders is x% or more of the overall total. I am struggling how to turn the rollup values into something I can run calculations on, rather than display. Any suggestions?
Thank you for this blog, you have gotten me further than anywhere else.
sorry for the delayed response. you can use a cte:
CREATE TABLE #TempTable( Account VARCHAR(20),
Sales NUMERIC(10,2)
)
INSERT INTO #TempTable VALUES ('North America', 10.00)
INSERT INTO #TempTable VALUES ('North America', 20.00)
INSERT INTO #TempTable VALUES ('North America', 30.00)
INSERT INTO #TempTable VALUES ('North America', 40.00)
INSERT INTO #TempTable VALUES ('North America', 50.00)
INSERT INTO #TempTable VALUES ('South America', 10.00)
INSERT INTO #TempTable VALUES ('South America', 20.00)
INSERT INTO #TempTable VALUES ('South America', 30.00)
INSERT INTO #TempTable VALUES ('South America', 40.00)
INSERT INTO #TempTable VALUES ('South America', 50.00)
;WITH myCte
AS
(
SELECT A.Account, A.Sales,
Total = (SELECT SUM(B.Sales) FROM #TempTable B WHERE B.Account = A.Account)
FROM #TempTable A
)
SELECT (Total * (30/100.0)), *
FROM myCte
WHERE Sales > (Total * (30/100.0))
Totally great explanaion. Few words, great examples. Even I could undestand it
. I do not think it can be any better. Thank you
very useful article! Thanks!
Simple but clear, Thank You, More power to you.
great article simply i loved it
great article i loved it