The Misunderstood CUBE Operator and the Generation of Grand Totals, Totals, and Subtotals

By Kalman Toth, M.Phil. Physics, M.Phil. Comp. Science, 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 in SQL Server applications. We will investigate the reason for its demise. First let's start with CUBE's "cousine", the ROLLUP operator.

-- GRAND TOTAL (all times), TOTAL (year), SUBTOTAL (quarter), DETAIL (month),

-- SQL group by with rollup

SELECT

[Year]=ISNULL(left(convert(char(8),OrderDate,112),4),''),

[Quarter]=ISNULL(left(convert(char(8),OrderDate,112),4)+ 'Q'+ CONVERT(char(1), (month(OrderDate)-1)/3+1),''),

[Month]=COALESCE(left(convert(char(8),OrderDate,112),4)+ 'Q'+ CONVERT(char(1), (month(OrderDate)-1)/3+1)+right('0'+convert(varchar,month(OrderDate)),2),''),

Sales='$'+CONVERT(varchar,SUM(SubTotal),1),

TotalType = CASE

WHEN GROUPING(left(convert(char(8),OrderDate,112),4))= 1 THEN 'GRAND TOTAL'

WHEN GROUPING(left(convert(char(8),OrderDate,112),4)+ 'Q'+ CONVERT(char(1), (month(OrderDate)-1)/3+1))= 1 THEN 'TOTAL - YY'

WHEN GROUPING(left(convert(char(8),OrderDate,112),4)+ 'Q'+ CONVERT(char(1), (month(OrderDate)-1)/3+1)+right('0'+convert(varchar,month(OrderDate)),2))= 1 THEN 'SUBTOTAL - QQ'

ELSE '' END

FROM Sales.SalesOrderHeader

GROUP BY

left(convert(char(8),OrderDate,112),4),

left(convert(char(8),OrderDate,112),4)+ 'Q'+ CONVERT(char(1), (month(OrderDate)-1)/3+1),

left(convert(char(8),OrderDate,112),4)+ 'Q'+ CONVERT(char(1), (month(OrderDate)-1)/3+1)+right('0'+convert(varchar,month(OrderDate)),2)

WITH ROLLUP

ORDER BY

GROUPING(left(convert(char(8),OrderDate,112),4)), [Year],

GROUPING(left(convert(char(8),OrderDate,112),4)+ 'Q'+ CONVERT(char(1), (month(OrderDate)-1)/3+1)), [Quarter],

GROUPING(left(convert(char(8),OrderDate,112),4)+ 'Q'+ CONVERT(char(1), (month(OrderDate)-1)/3+1)+right('0'+convert(varchar,month(OrderDate)),2)),[Month]

/* Results 

Year  Quarter     Month       Sales            TotalType

2001  2001Q3      2001Q307    $1,060,958.75    

2001  2001Q3      2001Q308    $2,357,932.10    

2001  2001Q3      2001Q309    $1,876,071.07    

2001  2001Q3                  $5,294,961.92     SUBTOTAL - QQ

2001  2001Q4      2001Q410    $1,528,473.54    

2001  2001Q4      2001Q411    $3,339,383.40    

2001  2001Q4      2001Q412    $2,803,291.70    

2001  2001Q4                  $7,671,148.64     SUBTOTAL - QQ

2001                          $12,966,110.56    TOTAL - YY

2002  2002Q1      2002Q101    $1,453,196.54    

2002  2002Q1      2002Q102    $2,833,324.01    

2002  2002Q1      2002Q103    $2,391,928.57    

2002  2002Q1                  $6,678,449.12     SUBTOTAL - QQ

2002  2002Q2      2002Q204    $1,724,736.56    

2002  2002Q2      2002Q205    $3,401,202.97    

2002  2002Q2      2002Q206    $2,304,182.76    

2002  2002Q2                  $7,430,122.29     SUBTOTAL - QQ

2002  2002Q3      2002Q307    $3,422,514.99    

2002  2002Q3      2002Q308    $4,917,293.51    

2002  2002Q3      2002Q309    $3,839,563.54    

2002  2002Q3                  $12,179,372.04    SUBTOTAL - QQ

2002  2002Q4      2002Q410    $2,582,992.53    

2002  2002Q4      2002Q411    $4,006,876.01    

2002  2002Q4      2002Q412    $3,208,617.85    

2002  2002Q4                  $9,798,486.39     SUBTOTAL - QQ

2002                          $36,086,429.83    TOTAL - YY

2003  2003Q1      2003Q101    $2,021,334.93    

2003  2003Q1      2003Q102    $3,353,516.28    

2003  2003Q1      2003Q103    $2,363,458.14    

2003  2003Q1                  $7,738,309.35     SUBTOTAL - QQ

2003  2003Q2      2003Q204    $2,752,819.39    

2003  2003Q2      2003Q205    $4,027,046.35    

2003  2003Q2      2003Q206    $2,947,979.81    

2003  2003Q2                  $9,727,845.55     SUBTOTAL - QQ

2003  2003Q3      2003Q307    $4,236,670.25    

2003  2003Q3      2003Q308    $6,131,997.32    

2003  2003Q3      2003Q309    $6,120,139.16    

2003  2003Q3                        $16,488,806.73    SUBTOTAL - QQ

2003  2003Q4      2003Q410    $3,840,150.73    

2003  2003Q4      2003Q411    $5,394,735.42    

2003  2003Q4      2003Q412    $5,957,314.93    

2003  2003Q4                  $15,192,201.07    SUBTOTAL - QQ

2003                          $49,147,162.71    TOTAL - YY

2004  2004Q1      2004Q101    $3,340,283.42    

2004  2004Q1      2004Q102    $4,712,382.32    

2004  2004Q1      2004Q103    $4,771,752.73    

2004  2004Q1                  $12,824,418.47    SUBTOTAL - QQ

2004  2004Q2      2004Q204    $4,274,109.22    

2004  2004Q2      2004Q205    $5,899,389.30    

2004  2004Q2      2004Q206    $6,077,766.33    

2004  2004Q2                  $16,251,264.84    SUBTOTAL - QQ

2004  2004Q3      2004Q307    $50,840.63 

2004  2004Q3                  $50,840.63        SUBTOTAL - QQ

2004                          $29,126,523.94    TOTAL - YY

                              $127,326,227.04   GRAND TOTAL

*/

We shall now proceed to CUBE. Lets start with a fairly simple example which involves some heavy-duty SQL:

-- GRAND TOTAL (all times), TOTAL (year), SUBTOTAL (month)

-- SQL derived table RPT generates the report

-- The outer query does the final filtering and sorting

USE AdventureWorks;

SELECT GRPMM, GRPYY, GRPALL,

       [YEAR]=YY, [MONTH]=MM, PURCHASES, POs=ORDERS

FROM

(

  SELECT  YY=COALESCE(CONVERT(varchar,YEAR(OrderDate)),''),

          MM=COALESCE(LEFT(CONVERT(varchar,OrderDate,111),7),''),

              ORDERS = COUNT(*),

              PURCHASES = '$'+CONVERT(varchar,SUM(TotalDue),1),

GRPMM = CASE WHEN

GROUPING(LEFT(CONVERT(varchar,OrderDate,111),7)) = 0

                       AND  GROUPING(YEAR(OrderDate)) = 1

                          THEN 'SUBTOTAL' ELSE '' END, 

      GRPYY= CASE WHEN GROUPING(YEAR(OrderDate)) = 0

AND    GROUPING(LEFT(CONVERT(varchar,OrderDate,111),7)) = 1

                         THEN 'TOTAL' ELSE '' END,

      GRPALL = CASE WHEN GROUPING(LEFT(CONVERT(varchar,OrderDate,111),7)) = 1

                        AND GROUPING(YEAR(OrderDate)) = 1

                                  THEN 'GRAND TOTAL' ELSE '' END

  FROM Purchasing.PurchaseOrderHeader

  GROUP BY YEAR(OrderDate), LEFT(CONVERT(varchar,OrderDate,111),7)

    WITH CUBE

) RPT

WHERE

       GRPMM != '' OR GRPYY !='' OR GRPALL !=''

ORDER BY

            CASE WHEN GRPALL!= '' THEN 3

                 WHEN GRPYY != '' THEN 2

            ELSE 1 END,

            YY, MM

GO

/* Partial results

GRPMM       GRPYY GRPALL      YEAR  MONTH       PURCHASES         POs

SUBTOTAL                            2004/05     $5,893,228.46     352

SUBTOTAL                            2004/06     $6,072,538.57     344

SUBTOTAL                            2004/07     $6,252,145.81     380

SUBTOTAL                            2004/08     $7,541,613.36     432

SUBTOTAL                            2004/09     $821,182.59       48

            TOTAL             2001              $114,804.88       8

            TOTAL             2002              $4,669,877.00     272

            TOTAL             2003              $17,183,341.46    1032

            TOTAL             2004              $46,130,536.49    2688

                  GRAND TOTAL                   $68,098,559.83    4000

*/

The GROUP BY ... WITH CUBE construct is rather simple, the complexity is due to segragating and sorting the results into 3 levels of dates (date dimension in data warehousing).

Let's continue with detail investigation of Grand Totals, Totals, Subtotals and more. In business reporting, 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. The business reporting custom is to prefix the word "Total" with a category ( dimension in data warehousing) like : ZipCode Total, County Total, State Total, Country Total, World Total (5 level geography dimension in data warehousing).

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:

Related articles:

Summarizing Data Using CUBE

Summarizing Data Using ROLLUP