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.
