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 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.
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.