Microsoft SQL Server 2005: The Complete Reference: Full Coverage of all New and Improved Features

The COMPUTE keyword directs SQL Server to generate totals that appear as additional summary columns at the end of result sets. When you use the COMPUTE with BY, the clause forces control-breaks and subtotals in the result set. You can also specify COMPUTE BY and COMPUTE in the same query. The syntax for the COMPUTE is as follows:

[COMPUTE {{AVG | COUNT | MAX | MIN | STDEV | STDEVP |VAR | VARP | SUM } ( expression ) } [ ,…n ] [ BY expression [ , …n ] ] ]

The following list of COMPUTE arguments specifies the aggregation to be performed:

• AVG   Average of the values in the numeric expression

• COUNT   Number of selected rows

• MAX   Highest value in the expression

• MIN   Lowest value in the expression

• STDEV   Statistical standard deviation for all values in the expression

• STDEVP   Statistical standard deviation for the population for all values in the expression

• SUM   Total of the values in the numeric expression

• VAR   Statistical variance for all values in the expression

• VARP   Statistical variance for the population for all values in the expression

Rules to consider when using the COMPUTE arguments:

• You cannot use the DISTINCT keyword with row aggregate functions when they are specified with the COMPUTE clause.

• When adding or averaging integer data, SQL Server will treat the result as an int value. This behavior persists, even if the data type of the column was set to smallint or tinyint. For more information about the return types of added or average data, see the BOL references to SUM and AVG.

• When you use a SELECT statement that includes a COMPUTE clause, the order of columns in the select list overrides the order of the aggregate functions in the COMPUTE clause. This order requirement is important for ODBC and DB-Library application programmers to remember, so that aggregate function results are placed in the correct place.

• The COMPUTE cannot be used in a SELECT INTO statement. COMPUTE statements generate tables, and their summary results do not get stored in the database. If you do use the COMPUTE in the SELECT INTO, SQL SERVER will ignore the COMPUTE results and they will not be installed in the new table.

• The COMPUTE clause cannot be used in a SELECT statement that is part of a DECLARE CURSOR statement.

The expression placeholder specifies a variable, such as the name of a column, on which the calculation is performed. The expression must appear in the select list and must be specified exactly the same as one of the expressions in the select list. As you can see from the syntax, you place the expression after the COMPUTE argument. A column alias specified in the select list obviously cannot be used within the COMPUTE expression.

Using BY

The BY expression is used to generate control-breaks and subtotals in the result set. Listing multiple expressions after BY breaks a group into subgroups and applies the aggregate function at each level of grouping. If you use the keywords COMPUTE BY, you must also use an ORDER BY clause. The expressions must be identical to or a subset of those listed after ORDER BY, and they must be in the same sequence. For example, if the ORDER BY clause is

ORDER BY s, 1, t

then the COMPUTE clause can be any (or all) of these:

COMPUTE BY s, 1, COMPUTE BY s, 1 COMPUTE BY s