SQLUSA

Microsoft SQL Server 2005 Articles

 

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:

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


 

 

The Best SQL Server 2005 Training in the World
 
 
SQLUSA.com Home Page