ROLLUP

The ROLLUP operator works in much the same way that the CUBE operator does. It is used with the GROUP BY clause and is specified the same way as CUBE, that is, GROUP BY… WITH ROLLUP. However, ROLLUP pays attention to the order in which columns are specified in the GROUP BY clause. It calculates subtotal and total information on all the possible combinations of columns in the GROUP BY clause from left to right.

For the last example, using ROLLUP in lieu of CUBE produces the following results:

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

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

(23       row(s)  affected)

The GROUP BY clause specifies grouping by store id, and then by order number. This grouping makes sense because one store may have many order numbers, but one order number will have no more than one store. The ROLLUP operator calculates subtotaled SUM(qty) by store. When all stores have been listed, it calculates a final SUM(qty) for all stores.

COMPUTE

Microsoft does not let you nest aggregates in the SELECT list. Suppose, for example, that you want to know the max(avg(price)) of grouped types in the titles table; that is, you want to know which type of book had the highest average price.

In order to calculate this, you will need to use the COMPUTE clause.

The COMPUTE clause allows you to calculate aggregates for a column that appears in your result set. The column used in COMPUTE must appear in the result set. SUM is the most frequently used aggregate with COMPUTE, when it is used to calculate totals for columns.

If you want to find the type of books with the highest average price, as mentioned earlier, here is how you can do it using COMPUTE:

select        type,   avg(price)
from          titles
group   by  type
compute        max(avg(price))

type
-------                  --------------------------
UNDECIDED        (null)
business        13.73
popular_comp    21.48
psychology      13.50
trad_cook       15.96

                max
                 ==========================
                21.48

(7 row(s)      affected)

To evaluate this query, the result set containing the types of books and their averages is generated by the SQL Server. When the result set is complete, the COMPUTE clause calculates the MAX() on the second column. This is displayed by ISQL/w as a double dashed line under which the COMPUTE result is displayed. Different programs display COMPUTE results differently. Some do not include the aggregate type. Some do not line up the aggregate under the appropriate column.

---

Note:  COMPUTE is a Transact-SQL extension to the ANSI standard. In application programs, you must take special steps (calling specialized functions that deal only with COMPUTE rows) to deal with computed results.

---

COMPUTE is useful for dealing with the rare nested aggregate problem. But you need not use grouping in order to use COMPUTE. In fact, you can compute any aggregate on any column that appears in your result set.