How to use ROLLUP in ‘Group By’ aggregate function in SQL Server Instance

The SQL Server GROUP BY ROLLUP is an extension of the GROUP BY clause that creates a group for each combination and adds results into sub-totals and grand totals.

ROLLUP does not create all possible grouping sets based on the dimension columns. When generating the grouping sets, ROLLUP assumes a hierarchy among the dimension columns and only generates grouping sets based on this hierarchy. For that reason, it is often used to generate subtotals and totals for reporting purposes.

Syntax

SELECT ...
FROM ...
[ ... ]
GROUP BY ROLLUP ( groupRollup [ , groupRollup [ , ... ] ] )
[ ... ]

Where

groupRollup ::= { <column_alias> | <position> | <expr> }
column_alias
   Column alias appearing in the query block’s SELECT list.
position
   Position of an expression in the SELECT list.
expr
   Any expression on tables in the current scope.

Examples

Let’s start with creating a test temp table with dummy data;

CREATE TABLE #Test
(
Country   VARCHAR (50),
State     VARCHAR (50),
City       VARCHAR (50),
Code     INT
)
INSERT INTO #Test VALUES('Hennepin', 'Minneapolis','EastMinneapolis',1 )
INSERT INTO #Test VALUES('Hennepin', 'Minneapolis','SouthMinneapolis',2 )
INSERT INTO #Test VALUES('Hennepin', 'Minneapolis','NorthMinneapolis',3)
INSERT INTO #Test VALUES('Hennepin', 'Minneapolis','WestMinneapolis',4)
INSERT INTO #Test VALUES('Hennepin', 'StPaul','EastStPaul',5)
INSERT INTO #Test VALUES('Hennepin', 'StPaul','WestStPaul',6)
INSERT INTO #Test VALUES('Hennepin', 'StPaul','SouthStPaul',7)
INSERT INTO #Test VALUES('Hennepin', 'StPaul','NorthStPaul',8)
INSERT INTO #Test VALUES('Hennepin', 'Minnetonka','WestMinnetonka',9)
INSERT INTO #Test VALUES('Hennepin', 'Minnetonka','EastMinnetonka',10)
INSERT INTO #Test VALUES('Hennepin', 'Minnetonka','SouthMinnetonka',11 )
INSERT INTO #Test VALUES('Hennepin', 'Minnetonka','NorthMinnetonka',12) 

Before we start let’s take a look at the table we created;

SELECT * FROM #Test;

GROUP BY ROLLUP

Simple example of the a group by clause with a Sum() aggregated function;

SELECT Country, State, City,
SUM (Code) AS Code
FROM #Test
GROUP BY Country, State, City;

GROUP BY ROLLUP

Now finally let’s use ROLLUP() function in addition to the group by clause;

SELECT Country, State, City,
SUM (Code) AS Code
FROM #Test
GROUP BY Country, State, City WITH ROLLUP;

Or same script can also be written as;

SELECT Country, State, City,
SUM (Code) AS Code
FROM #Test
GROUP BY ROLLUP (Country, State, City);

GROUP BY ROLLUP

As you can see the output above, one row is produced for each unique grouping set and an aggregated grand total row is produced with NULL values.

Please contact us  if you would like us to help you with your next business intelligence project.

Scroll to top