CUBE

In the first example today, I calculated the average price for each type of book. The CUBE operator, appended to the GROUP BY clause, would calculate the AVG(price) for all books:

select   type,    avg(price)    Average
from       titles
group  by  type   with   cube

type              Average
-------                    -----------------------
UNDECIDED         (null)
business         13.73
mod_cook         11.49
popular_comp     21.48
psychology       13.50
trad_cook        15.96
(null)           14.77

(7   row(s)   affected)

The results look the same for the first six rows. Then, the CUBE is performed. The CUBE runs against all rows in the titles table that match the WHERE conditions—in this case, all of the rows. This extra row is then added into the worktable containing the grouped columns, and all seven rows are returned. Notice how the rowcount quoted by ISQL/w includes six grouped columns and the seventh, contrived CUBE result. The CUBE result comes last, unless an ORDER BY is performed.

This is a simple example of CUBE, which returns one CUBE row. (There is only one column in the GROUP BY list on which to operate.)

CUBE performs more work when there are multiple columns listed in the GROUP BY list. Here is the last GROUP BY example I presented, this time incorporating the CUBE operator:

select        stor_id,  ord_num,  sum(qty)  “Books Ordered”
from           sales
group   by    stor_id, ord_num  with  cube

stor_id   ord_num                      Books Ordered
-------         ------------------      -----------
6380       6871                      5
6380       722a                      3
6380       (null)                    8

7066       A2976                     50
7066       QA7442.3                  75
7066       (null)                    125

7067       D4482                     10
7067       P2121                     80
7067       (null)                    90

7131       N914008                   20
7131       N914014                   25
7131       P3087a                    85
7131       (null)                    130

7896       QQ2299                    15
7896       TQ456                     10
7896       X999                      35
7896       (null)                    60

8042       423LL922                  15
8042       423LL930                  10
8042       P723                      25
8042       QA879.1                   30
8042       (null)                    80

(null)     (null)                    493
(null)     423LL922                  15
(null)     423LL930                  10
(null)     6871                      5
(null)     722a                      3
(null)     A2976                     50
(null)     D4482                     10
(null)     N914008                   20
(null)     N914014                   25
(null)     P2121                     80
(null)     P3087a                    85
(null)     P723                      25
(null)     QA7442.3                  75
(null)     QA879.1                   30
(null)     QQ2299                    15
(null)     TQ456                     10
(null)     X999                      35

(39       row(s)    affected)

The line breaks are not really there when you use ISQL/w with the CUBE operator. I have put them in to make it easier to read the output and see where the CUBE rows are occurring.

Take a look at the first three rows in this result set. The first two of those three are separate order numbers for a single store. In those order numbers, the total number of books ordered was five books for the first order and three for the second order. The CUBE operator then calculates the SUM of all books ordered for this store, which in this case is (5 + 3 = 8). This subtotal is added as a new row to the worktable created by the server for the sole purpose of evaluating this query.

For each store, this subtotaling procedure is repeated, where the SUM for each order is calculated, and then the CUBE for each store is calculated. Finally, right in the middle of the report, the SUM for all stores and orders is calculated with a result of 493. Now the CUBE operator runs against each order.

Because of the way the data is organized (order numbers are subordinate to stores), the CUBE data for the last 20 or so rows, where the data for each order number is calculated, doesn’t give you anything new. We would have been better off using the ROLLUP operator.