DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS

 

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:

Class Style Color Items AvgListPrice
 U  U  UNKNOWN 200 $5.66
 U  U Black 5 $99.60
 U  U Blue 1 $34.99
 U  U Grey 1 $125.00
 U  U Red 1 $34.99
 U  U Silver 13 $38.55
 U  U Silver/Black 1 $80.99
 U  U z Color Total 222 $10.85


This is the final rollup line:

Class Style Color Items AvgListPrice
z Class Total z Style Total z Color Total 504 $438.67

 

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:

Class Style Color Items AvgListPrice
z Class Total 504 $438.67

 

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:

ID Class Style Color Items AvgListPrice Name
1  U  U  UNKNOWN 200 $5.66 Adjustable Race
2  U  U Black 5 $99.60 Chainring
3  U  U Blue 1 $34.99 Sport-100 Helmet, Blue
4  U  U Grey 1 $125.00 Touring-Panniers, Large
5  U  U Red 1 $34.99 Sport-100 Helmet, Red
6  U  U Silver 13 $38.55 Chain
7  U  U Silver/Black 1 $80.99 Touring Pedal
8  U  U Color Total 222 $10.85 Adjustable Race
9  U Black 4 $59.99 Men's Sports Shorts, L
10  U Multi 3 $89.99 Men's Bib-Shorts, L
11  U Color Total 7 $72.85 Men's Bib-Shorts, L
12  U Black 6 $31.24 Full-Finger Gloves, L
13  U Blue 3 $63.50 Classic Vest, L
14  U Multi 5 $41.79 AWC Logo Cap
15  U White 4 $9.25 Mountain Bike Socks, L
16  U Yellow 4 $53.99 Short-Sleeve Classic Jersey, L
17  U Color Total 22 $38.17 AWC Logo Cap
18  U Black 6 $72.49 Women's Mountain Shorts, L
19  U Color Total 6 $72.49 Women's Mountain Shorts, L
20  U  UNKNOWN 200 $5.66 Adjustable Race
21  U Black 21 $64.78 Chainring
22  U Blue 4 $56.37 Classic Vest, L
23  U Grey 1 $125.00 Touring-Panniers, Large
24  U Multi 8 $59.87 AWC Logo Cap
25  U Red 1 $34.99 Sport-100 Helmet, Red
26  U Silver 13 $38.55 Chain
27  U Silver/Black 1 $80.99 Touring Pedal
28  U White 4 $9.25 Mountain Bike Socks, L
29  U Yellow 4 $53.99 Short-Sleeve Classic Jersey, L
30  U Style Total 257 $16.31 Adjustable Race
31  U  UNKNOWN 11 $93.94 HL Bottom Bracket
32  U Black 5 $343.91 HL Crankset
33  U Silver/Black 2 $80.99 HL Mountain Pedal
34  U Color Total 18 $161.94 HL Bottom Bracket
35 Black 21 $2,098.28 HL Mountain Frame - Black, 38
36 Blue 8 $1,693.99 HL Touring Frame - Blue, 46
37 Red 15 $2,416.92 HL Road Frame - Red, 44
38 Silver 12 $2,281.87 HL Mountain Frame - Silver, 38
39 Yellow 8 $1,693.99 HL Touring Frame - Yellow, 46
40 Color Total 64 $2,106.31 HL Mountain Frame - Black, 38
41  UNKNOWN 11 $93.94 HL Bottom Bracket
42 Black 26 $1,760.90 HL Crankset
43 Blue 8 $1,693.99 HL Touring Frame - Blue, 46
44 Red 15 $2,416.92 HL Road Frame - Red, 44
45 Silver 12 $2,281.87 HL Mountain Frame - Silver, 38
46 Silver/Black 2 $80.99 HL Mountain Pedal
47 Yellow 8 $1,693.99 HL Touring Frame - Yellow, 46
48 Style Total 82 $1,679.50 HL Bottom Bracket
49  U  UNKNOWN 21 $42.83 LL Bottom Bracket
50  U Black 6 $87.02 LL Crankarm
51  U Silver/Black 2 $40.49 LL Mountain Pedal
52  U Color Total 29 $51.81 LL Bottom Bracket
53 Black 26 $493.47 LL Mountain Frame - Black, 40
54 Blue 10 $537.89 LL Touring Frame - Blue, 44
55 Red 12 $560.11 LL Road Frame - Red, 44
56 Silver 10 $414.52 LL Mountain Frame - Silver, 40
57 Yellow 10 $537.89 LL Touring Frame - Yellow, 44
58 Color Total 68 $506.68 LL Mountain Frame - Black, 40
59  UNKNOWN 21 $42.83 LL Bottom Bracket
60 Black 32 $417.26 LL Crankarm
61 Blue 10 $537.89 LL Touring Frame - Blue, 44
62 Red 12 $560.11 LL Road Frame - Red, 44
63 Silver 10 $414.52 LL Mountain Frame - Silver, 40
64 Silver/Black 2 $40.49 LL Mountain Pedal
65 Yellow 10 $537.89 LL Touring Frame - Yellow, 44
66 Style Total 97 $370.69 LL Bottom Bracket
67  U  UNKNOWN 16 $69.79 ML Bottom Bracket
68  U Black 6 $204.22 ML Crankarm
69  U Silver/Black 2 $62.09 ML Mountain Pedal
70  U Color Total 24 $102.76 ML Bottom Bracket
71 Black 8 $714.38 ML Mountain Frame - Black, 38
72 Blue 4 $1,214.85 Touring-2000 Blue, 46
73 Red 10 $1,026.41 ML Road Frame - Red, 44
74 Color Total 22 $947.20 ML Mountain Frame - Black, 38
75 Silver 8 $566.79 ML Mountain Frame-W - Silver, 38
76 Yellow 14 $1,098.61 ML Road Frame-W - Yellow, 38
77 Color Total 22 $905.22 ML Mountain Frame-W - Silver, 38
78  UNKNOWN 16 $69.79 ML Bottom Bracket
79 Black 14 $495.74 ML Crankarm
80 Blue 4 $1,214.85 Touring-2000 Blue, 46
81 Red 10 $1,026.41 ML Road Frame - Red, 44
82 Silver 8 $566.79 ML Mountain Frame-W - Silver, 38
83 Silver/Black 2 $62.09 ML Mountain Pedal
84 Yellow 14 $1,098.61 ML Road Frame-W - Yellow, 38
85 Style Total 68 $635.58 ML Bottom Bracket
86  U  UNKNOWN 248 $16.86 Adjustable Race
87  U Black 22 $180.23 Chainring
88  U Blue 1 $34.99 Sport-100 Helmet, Blue
89  U Grey 1 $125.00 Touring-Panniers, Large
90  U Red 1 $34.99 Sport-100 Helmet, Red
91  U Silver 13 $38.55 Chain
92  U Silver/Black 7 $64.02 HL Mountain Pedal
93  U 293 $31.71 Adjustable Race
94 Black 4 $59.99 Men's Sports Shorts, L
95 Multi 3 $89.99 Men's Bib-Shorts, L
96 7 $72.85 Men's Bib-Shorts, L
97 Black 61 $1,029.45 Full-Finger Gloves, L
98 Blue 25 $959.23 Classic Vest, L
99 Multi 5 $41.79 AWC Logo Cap
100 Red 37 $1,438.89 HL Road Frame - Red, 44
101 Silver 22 $1,433.07 HL Mountain Frame - Silver, 38
102 White 4 $9.25 Mountain Bike Socks, L
103 Yellow 22 $870.31 HL Touring Frame - Yellow, 46
104 176 $1,084.87 AWC Logo Cap
105 Black 6 $72.49 Women's Mountain Shorts, L
106 Silver 8 $566.79 ML Mountain Frame-W - Silver, 38
107 Yellow 14 $1,098.61 ML Road Frame-W - Yellow, 38
108 28 $726.78 ML Mountain Frame-W - Silver, 38
109  UNKNOWN 248 $16.86 Adjustable Race
110 Black 93 $725.12 Chainring
111 Blue 26 $923.68 Classic Vest, L
112 Grey 1 $125.00 Touring-Panniers, Large
113 Multi 8 $59.87 AWC Logo Cap
114 Red 38 $1,401.95 HL Road Frame - Red, 44
115 Silver 43 $850.31 Chain
116 Silver/Black 7 $64.02 HL Mountain Pedal
117 White 4 $9.25 Mountain Bike Socks, L
118 Yellow 36 $959.09 HL Touring Frame - Yellow, 46
119 Class Total 504 $438.67 Adjustable Race

Related articles:

Summarizing Data Using CUBE

Summarizing Data Using ROLLUP

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE