The Misunderstood CUBE Operator and the Generation of Grand Total, Total, and Subtotal

By Kalman Toth, M.Phil., M.Phil., MCDBA, MCITP

October 7, 2007

CUBE is an extraordinary function. It automatically generates a summation rollup to the top level of a GROUP BY. Anything automatic sounds good. A smart automatic function sounds even better. Yet, the CUBE operator remains underutilized. We will investigate the reason for its demise.

There is the usual hierarchical summation list of Grand Total, Total, and Subtotal. Have you ever wondered why only 3? What is below Subtotal? Minitotal? Where would Super Total fit in? If we have weekly Subtotal of daily data, followed by monthly Total and Yearly Grand Total, we are fine. But what if we introduce quarterly totals as well? It appears that the trio can only be used for 3 levels of summation. Beyond that we have to invent our own terminology to indicate the level of summation.

Returning to the CUBE operator, the first trouble with it is that the summation labels are all NULLs. That feature dooms its reporting value practically worthless. So either we have to use the ISNULL function, possibly COALESCE, to try to put in some meaning, or even better, apply the GROUPING function. Here is an example from AdventureWorks the database:

SELECT CASE WHEN (GROUPING(Class) = 1) THEN 'z Class Total'
ELSE ISNULL(Class, ' UNKNOWN')
END AS Class,
CASE WHEN (GROUPING(Style) = 1) THEN 'z Style Total'
ELSE ISNULL(Style, ' UNKNOWN')
END AS Style,
CASE WHEN (GROUPING(Color) = 1) THEN 'z Color Total'
ELSE ISNULL(Color, ' UNKNOWN')
END AS Color, count(*) AS Items,
'$'+convert(varchar,avg(ListPrice),1) AS AvgListPrice
FROM Production.Product
GROUP BY Class, Style, Color
WITH CUBE
ORDER BY Class, Style, Color

This is a 3-level non-hierarchival GROUP BY. Because it is non-hierarchival we could not use the Subtotal, Total and Grand Total terminology for the rollup, although sequence wise it would have been a fit.

You may have noticed that the "UNKNOWN" has a space preceding it and the "totals" are preceded by "z ". The reason is sorting. The missing data is sorted to the top and the rollups are sorted to the bottom in a meaningful way, assuming of course there is no data which conflicts with our intention.

Here is a detail line output:

This is the final rollup line:

We are still in hot water as far as reporting is concerned. The cell with content "504" cannot have three different names. Basically that is the Class Total. The Grand Total! We should blank out the inner totals. Same applies to other rows where there is more than one total on the same line.

Returning to the drawing board, this is the new query:

SELECT CASE WHEN (GROUPING(Class) + Grouping(Style)+Grouping(Color)= 3) THEN 'z Class Total'
WHEN (GROUPING(Class)=1 and (Grouping(Style)=0 or Grouping(Color)=0)) THEN 'z '
ELSE ISNULL(Class, ' UNKNOWN')
END AS Class,

CASE WHEN (GROUPING(Style) = 1 and GROUPING(Class) = 1) THEN 'z '
WHEN (GROUPING(Style) = 1 and GROUPING(Color) = 0) THEN 'z '
WHEN (GROUPING(Style) = 1 and GROUPING(Color) = 1) THEN 'z Style Total'
ELSE ISNULL(Style, ' UNKNOWN')
END AS Style,
CASE WHEN (GROUPING(Color) = 1 and GROUPING(Class) = 1) THEN 'z '
WHEN (GROUPING(Color) = 1 and GROUPING(Style) = 1) THEN 'z '
WHEN (GROUPING(Color) = 1 and GROUPING(Style) = 0) THEN 'z Color Total'
ELSE ISNULL(Color, ' UNKNOWN')
END AS Color, count(*) AS Items,
'$'+convert(varchar,avg(ListPrice),1) AS AvgListPrice
FROM Production.Product
GROUP BY Class, Style, Color
WITH CUBE
ORDER BY Class, Style, Color

This is the final line:

Similarly, other redundant totals are "z"-d out.

We are almost there; just have to get rid of the "z"-s. To do that, we introduce a temp table to hold the results for us for further manipulation. We added min(Name) from a group of product names to lend more reality to the report:

SELECT IDENTITY(int,1,1) AS ID,
CASE WHEN (GROUPING(Class) + Grouping(Style)+Grouping(Color)= 3) THEN 'z Class Total'
WHEN (GROUPING(Class)=1 and (Grouping(Style)=0 or Grouping(Color)=0)) THEN 'z '
ELSE ISNULL(Class, ' UNKNOWN')
END AS Class,

CASE WHEN (GROUPING(Style) = 1 and GROUPING(Class) = 1) THEN 'z '
WHEN (GROUPING(Style) = 1 and GROUPING(Color) = 0) THEN 'z '
WHEN (GROUPING(Style) = 1 and GROUPING(Color) = 1) THEN 'z Style Total'
ELSE ISNULL(Style, ' UNKNOWN')
END AS Style,
CASE WHEN (GROUPING(Color) = 1 and GROUPING(Class) = 1) THEN 'z '
WHEN (GROUPING(Color) = 1 and GROUPING(Style) = 1) THEN 'z '
WHEN (GROUPING(Color) = 1 and GROUPING(Style) = 0) THEN 'z Color Total'
ELSE ISNULL(Color, ' UNKNOWN')
END AS Color, count(*) AS Items,
'$'+convert(varchar,avg(ListPrice),1) AS AvgListPrice
Name=min(Name)
INTO #ProductReport
FROM Production.Product
GROUP BY Class, Style, Color
WITH CUBE
ORDER BY Class, Style, Color

We follow up with updates to get rid of the "z"-s:

UPDATE #ProductReport SET Class = '' WHERE Class = 'z '
UPDATE #ProductReport SET Style = '' WHERE Style = 'z '
UPDATE #ProductReport SET Color = '' WHERE Color = 'z '
UPDATE #ProductReport SET Class = 'Class Total' WHERE Class = 'z Class Total'
UPDATE #ProductReport SET Style = 'Style Total' WHERE Style = 'z Style Total'
UPDATE #ProductReport SET Color = 'Color Total' WHERE Color = 'z Color Total'

At last, we can generate the final report:

SELECT * FROM #ProductReport
ORDER BY ID

So we got an idea of what is behind the unpopularity of the CUBE operator: it is the difficulty of naming the rollup cells properly.

Here is the final report: