# COMPUTE (Transact-SQL)

Generates totals that appear as additional summary columns at the end of the result set. When used with BY, the COMPUTE clause generates control-breaks and subtotals in the result set. You can specify COMPUTE BY and COMPUTE in the same query.

Transact-SQL Syntax Conventions

## Syntax

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

## Arguments

AVG | COUNT | MAX | MIN | STDEV | STDEVP | VAR | VARP | SUM

Specifies the aggregation to be performed. These row aggregate functions are used with the COMPUTE clause.Row aggregate function Result 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

There is no equivalent to COUNT(*). To find the summary information produced by GROUP BY and COUNT(*), use a COMPUTE clause without BY.

These functions ignore null values.

The DISTINCT keyword is not allowed with row aggregate functions when they are specified with the COMPUTE clause.

When you add or average integer data, Microsoft SQL Server 2005 Database Engine treats the result as an

**int**value, even if the data type of the column is**smallint**or**tinyint**. For more information about the return types of added or average data, see SUM (Transact-SQL) and AVG (Transact-SQL).Note

To reduce the chance of overflow errors in ODBC and DB-Library programs, make all variable declarations for the results of averages or sums the data type

**int**.

*expression*

An Expressions (Transact-SQL), such as the name of a column on which the calculation is performed.*expression*must appear in the select list and must be specified identical to one of the expressions in the select list. A column alias specified in the select list cannot be used within*expression*.Note

**ntext**,**text**, or**image**data types cannot be specified in a COMPUTE or COMPUTE BY clause.

BY

*expression*

Generates control-breaks and subtotals in the result set.*expression*is an identical copy of an*order_by_expression*in the associated ORDER BY clause. Typically, this is a column name or column alias. Multiple expressions can be specified. Listing multiple expressions after BY breaks a group into subgroups and applies the aggregate function at each level of grouping.If you use COMPUTE BY, you must also use an ORDER BY clause. The expressions must be the same as or a subset of those listed after ORDER BY, and must be in the same sequence. For example, if the ORDER BY clause is

`ORDER BY a, b, c`

,`the COMPUTE clause can be any, or all, of the following:`

`COMPUTE BY a, b, c COMPUTE BY a, b COMPUTE BY a`

Note

In a SELECT statement with a COMPUTE clause, the order of columns in the select list overrides the order of the aggregate functions in the COMPUTE clause. ODBC and DB-Library programmers must know about this order requirement to put the aggregate function results in the correct place.

You cannot use COMPUTE in a SELECT INTO statement because statements including COMPUTE generate tables and their summary results are not stored in the database. Therefore, any calculations produced by COMPUTE do not appear in the new table created with the SELECT INTO statement.

You cannot use the COMPUTE clause when the SELECT statement is part of a DECLARE CURSOR statement.

## Examples

### A. Using COMPUTE in query to return totals

In the following example, the `SELECT`

statement uses a simple `COMPUTE`

clause to produce a grand total of the sum of the `SubTotal`

and `TotalDue`

from the `SalesOrderHeader`

table.

```
USE AdventureWorks;
GO
SELECT CustomerID, OrderDate, SubTotal, TotalDue
FROM Sales.SalesOrderHeader
WHERE SalesPersonID = 35
ORDER BY OrderDate
COMPUTE SUM(SubTotal), SUM(TotalDue);
```

### B. Using COMPUTE in query to return totals

In the following example, the `SELECT`

statement uses a `COMPUTE`

clause to produce totals per salesperson of the sum of the `SubTotal`

and `TotalDue`

from the `SalesOrderHeader`

table.

```
USE AdventureWorks;
GO
SELECT SalesPersonID, CustomerID, OrderDate, SubTotal, TotalDue
FROM Sales.SalesOrderHeader
ORDER BY SalesPersonID, OrderDate
COMPUTE SUM(SubTotal), SUM(TotalDue) BY SalesPersonID;
```

## See Also

#### Reference

SELECT (Transact-SQL)

ORDER BY Clause (Transact-SQL)