If you have been writing queries that use ROLLUP, you are probably aware that the aggregated rows return NULL for the column that you are grouping by. What if you already have a NULL value in that column, how can you know which row is the aggregated row? Let’s take a look, first create this table

T-SQL
1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE TestRollup(Country VARCHAR(20),Col1 INT, col2 INT)
INSERT TestRollup VALUES('United States',20,10)
INSERT TestRollup VALUES('United States',30,90)
 
INSERT TestRollup VALUES('Denmark',20,10)
INSERT TestRollup VALUES('Denmark',44,33)
 
INSERT TestRollup VALUES('Zimbabwe',20,10)
INSERT TestRollup VALUES('Zimbabwe',20,10)
INSERT TestRollup VALUES('Zimbabwe',20,1000)
INSERT TestRollup VALUES('Zimbabwe',2000,10)
CREATE TABLE TestRollup(Country VARCHAR(20),Col1 INT, col2 INT)
INSERT TestRollup VALUES('United States',20,10)
INSERT TestRollup VALUES('United States',30,90)

INSERT TestRollup VALUES('Denmark',20,10)
INSERT TestRollup VALUES('Denmark',44,33)

INSERT TestRollup VALUES('Zimbabwe',20,10)
INSERT TestRollup VALUES('Zimbabwe',20,10)
INSERT TestRollup VALUES('Zimbabwe',20,1000)
INSERT TestRollup VALUES('Zimbabwe',2000,10)

Now let’s do our simple ROLLUP query

T-SQL
1
2
3
SELECT Country, SUM(Col1) Col1Sum, SUM(col2) AS Col2Sum
FROM TestRollup
GROUP BY Country WITH ROLLUP
SELECT Country, SUM(Col1) Col1Sum, SUM(col2) AS Col2Sum
FROM TestRollup
GROUP BY Country WITH ROLLUP

Here is the results

Country Col1Sum Col2Sum
Denmark 64 43
United States 50 100
Zimbabwe 2060 1030
NULL 2174 1173

We can easily determine that the Country column that has the value NULL is the total. What happens when we add the following row

T-SQL
1
INSERT TestRollup VALUES(NULL,2000,3000)
INSERT TestRollup VALUES(NULL,2000,3000)

Now when we run the same query again, we have two rows where Country is NULL

T-SQL
1
2
3
SELECT Country, SUM(Col1) Col1Sum, SUM(col2) AS Col2Sum
FROM TestRollup
GROUP BY Country WITH ROLLUP
SELECT Country, SUM(Col1) Col1Sum, SUM(col2) AS Col2Sum
FROM TestRollup
GROUP BY Country WITH ROLLUP

Here are the results

Country Col1Sum Col2Sum
NULL 2000 3000
Denmark 64 43
United States 50 100
Zimbabwe 2060 1030
NULL 4174 4173

To figure out which of the two is the one that is caused by ROLLUP, you can use the GROUPING function, the function will return 1 if it is aggregated and 0 otherwise.

Here is what Books On Line has to say about GROUPING

GROUPING
Indicates whether a specified column expression in a GROUP BY list is aggregated or not. GROUPING returns 1 for aggregated or 0 for not aggregated in the result set. GROUPING can be used only in the SELECT <select> list, HAVING, and ORDER BY clauses when GROUP BY is specified.

GROUPING is used to distinguish the null values that are returned by ROLLUP, CUBE or GROUPING SETS from standard null values. The NULL returned as the result of a ROLLUP, CUBE or GROUPING SETS operation is a special use of NULL. This acts as a column placeholder in the result set and means all.

Now, let’s add GROUPING(Country) to our query

T-SQL
1
2
3
SELECT Country, SUM(Col1) Col1Sum, SUM(col2) AS Col2Sum, GROUPING(Country) AS GroupingCountry
FROM TestRollup
GROUP BY Country WITH ROLLUP
SELECT Country, SUM(Col1) Col1Sum, SUM(col2) AS Col2Sum, GROUPING(Country) AS GroupingCountry
FROM TestRollup
GROUP BY Country WITH ROLLUP

Here are the results, as you can see the function returns 1 for the aggregated row

Country Col1Sum Col2Sum GroupingCountry
NULL 2000 3000 0
Denmark 64 43 0
United States 50 100 0
Zimbabwe 2060 1030 0
NULL 4174 4173 1

Now we can simply add a CASE expression to display Total for the aggregated column

T-SQL
1
2
3
SELECT CASE  GROUPING(Country) WHEN 1 THEN 'Total' ELSE Country END Country, SUM(Col1) Col1Sum, SUM(col2) AS Col2Sum
FROM TestRollup
GROUP BY Country WITH ROLLUP
SELECT CASE  GROUPING(Country) WHEN 1 THEN 'Total' ELSE Country END Country, SUM(Col1) Col1Sum, SUM(col2) AS Col2Sum
FROM TestRollup
GROUP BY Country WITH ROLLUP

Here is what the results look like

Country Col1Sum Col2Sum
NULL 2000 3000
Denmark 64 43
United States 50 100
Zimbabwe 2060 1030
Total 4174 4173

That is it for this post, hopefully it will help someone

*** Remember, if you have a SQL related question, try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum