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 |
|---|
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 |
|---|
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
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
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 |
|---|
Now we can simply add a CASE expression to display Total for the aggregated column
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 |
|---|
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
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
