Summarizing groups of data: the compute clause

The compute clause is a Transact-SQL extension. Use it with row aggregates to produce reports that show subtotals of grouped summaries. Such reports, usually produced by a report generator, are called control-break reports, since summary values appear in the report under the control of the groupings (“breaks”) you specify in the compute clause.

These summary values appear as additional rows in the query results, unlike the aggregate results of a group by clause, which appear as new columns.

A compute clause allows you to see detail and summary rows with a single select statement. You can calculate summary values for subgroups and you can calculate more than one row aggregate (see “Row aggregates and compute”) for the same group.

The general syntax for compute is:

compute row_aggregate(column_name) 
     [, row_aggregate(column_name)]... 
     [by column_name [, column_name]...] 

The row aggregates you can use with compute are sum, avg, min, max, and count, and count_big. You can use sum and avg only with numeric columns. Unlike the order by clause, you cannot use the positional number of a column from the select list instead of the column name.

NoteYou cannot use text, unitext, or image columns in a compute clause.

A system test may fail because there are too many aggregates in the compute clause of a query. The number of aggregates that each compute clause can accommodate is limited to 127, and if a compute clause contains more than 127 aggregates, the system generates an error message when you try to execute the query.

Each avg aggregate counts as two aggregates when you are counting toward the limit of 127, because an avg aggregate is actually a combination of a sum aggregate and a count aggregate.

Following are two queries and their results. The first one uses group by and aggregates. The second uses compute and row aggregates. Notice the difference in the results.

select type, sum(price), sum(advance) 
from titles 
group by type 
type 
------------   -------    ----------
UNDECIDED         NULL         NULL 
business         54.92    25,125.00 
mod_cook         22.98    15,000.00 
popular_comp     42.95    15,000.00 
psychology       67.52    21,275.00 
trad_cook        47.89    19,000.00 
 
(6 rows affected) 
select type, price, advance 
from titles 
order by type 
compute sum(price), sum(advance) by type 
type         price                    advance
------------ ------------------------ --------
UNDECIDED    NULL                     NULL
 
Compute Result:
------------------------ ------------------------
                   NULL                      NULL
 
type         price                advance 
------------ -------------------- ----------
business     2.99                 10,125.00
business    11.95                  5,000.00
business    19.99                  5,000.00
business    19.99                  5,000.00
 
Compute Result:
------------------------ ------------------------
                   54.92                25,125.00
type         price                   advance
------------ ----------------------- ---------
mod_cook      2.99                   15,000.00
mod_cook     19.99                        0.00
 
Compute Result:
------------------------ ------------------------
                   22.98                15,000.00
 
type          price               advance
------------- ------------------- ------------
popular_comp  NULL                        NULL
popular_comp  20.00                   8,000.00
popular_comp  22.95                   7,000.00
 
Compute Result:
------------------------ ------------------------
                   42.95                15,000.00
 
type         price                    advance 
------------ ------------------------ --------
psychology    7.00                    6,000.00
psychology    7.99                    4,000.00
psychology   10.95                    2,275.00
psychology   19.99                    2,000.00
psychology   21.59                    7,000.00
 
Compute Result:
------------------------ ------------------------
                    67.52               21,275.00
 
type         price                   advance
------------ ----------------------- --------
trad_cook    11.95                   4,000.00
trad_cook    14.99                   8,000.00
trad_cook    20.95                   7,000.00
 
Compute Result:
------------------------ ------------------------
                    47.89               19,000.00
 
(24 rows affected)

Each summary value is treated as a row.