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

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

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

INSERT TestRollup VALUES(NULL,2000,3000)

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

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