MS Sql Server Queries: Grouping with ROLLUP or CUBE

Working on a GROUP BY query I decided I needed ROLLUP. That bit is pretty easy:
SELECT X, Y, COUNT(*) AS vol 
FROM Tbl 
GROUP BY X, Y WITH ROLLUP
The sub-total lines contain NULL in the X and Y columns. This all applies to CUBE as well.

However, in this case, X and Y contained NULLs, and that makes the output confusing. I remembered there was a way to change the X and Y values in the sub-total lines and had to google for it. Here is the syntax as a reminder to self!

SELECT   CASE WHEN (GROUPING(X) = 1) THEN 'ALL' ELSE X END AS X,
         CASE WHEN (GROUPING(Y) = 1) THEN 'ALL' ELSE Y END AS Y,
         COUNT(*) AS vol
FROM     Tbl
GROUP BY X, Y WITH ROLLUP

The GROUPING(colname) function returns 1 when on a sub-total line and 0 when on a data line.

One little caveat! Where I have put the value 'ALL' - that needs to be the same datatype as the column you are testing. Alternatively, convert/cast the column as a string.

A more standards-compliant syntax for the GROUP BY ... ROLLUP clause, which is supported by later versions of MS SQL Server, is:

GROUP BY ROLLUP(X, Y)

Reminder:

ROLLUP gives you sub-totals and a grand-total based on the GROUP BY hierarchy.

CUBE gives you sub-totals and a grand-total based on every possible combination in the GROUP BY list.