Use COMPUTE BY with row aggregate functions to produce reports that summarize values whenever the value in a specified column changes. 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, or breaks, you specify in the COMPUTE BY clause.
These summary values appear as additional rows in the query results, unlike the aggregate function results of a GROUP BY clause, which appear as new columns.
A COMPUTE BY clause allows you to see both detail and summary rows with one SELECT statement. You can calculate summary values for subgroups, and you can calculate more than one row aggregate function for the same group.
The COMPUTE BY clause has the following syntax:
COMPUTE row_aggregate(column_name)
[, row_aggregate(column_name)...]
[BY column_name [, column_name]...]
You can use the aggregate functions SUM, AVG, MIN, MAX, and COUNT with COMPUTE BY. SUM and AVG are used with numeric columns only.
Note You cannot use COMPUTE BY with text or image columns.
This query uses GROUP BY and aggregate functions:
SELECT type, SUM(price), SUM(advance) |
FROM titles |
GROUP BY type |
This query uses COMPUTE BY and row aggregate functions:
SELECT type, price, advance
FROM titles
ORDER BY type
COMPUTE SUM(price), SUM(advance) BY type
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
sum
==========================
54.92
sum
==========================
25,125.00
type price advance
------------ -------------------------- --------------------------
mod_cook 2.99 15,000.00
mod_cook 19.99 0.00
sum
==========================
22.98
sum
==========================
15,000.00
type price advance
------------ -------------------------- --------------------------
popular_comp (null) (null)
popular_comp 20.00 8,000.00
popular_comp 22.95 7,000.00
sum
==========================
42.95
sum
==========================
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
sum
==========================
67.52
sum
==========================
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
sum
==========================
47.89
sum
==========================
19,000.00
type price advance
------------ -------------------------- --------------------------
UNDECIDED (null) (null)
sum
==========================
(null)
sum
==========================
(null)
(24 row(s) affected)
The summary values are treated as new rows, which is why the SQL Server message says "24 rows affected". (For a complete explanation, rules, and more examples of the COMPUTE clause, see Displaying Totals with GROUP BY or COMPUTE.)