Transact-SQL User's Guide

Home

Summarizing
groups of data: the compute clause

Specifying more than one column after compute

Transact-SQL User's Guide

Chapter 3 Using Aggregates, Grouping, and Sorting

Summarizing groups of data: the compute clause

Row aggregates and compute

The row aggregates used with compute are listed in Table 3-2:

How aggregates are used with a compute statement
Row aggregates	Result
sum	Total of the values in the expression
avg	Average of the values in the expression
max	Highest value in the expression
min	Lowest value in the expression
count	Number of selected rows

These row aggregates are the same aggregates that can be used with group by, except there is no row aggregate function that is the equivalent of count(*). To find the summary information produced by group by and count(*), use a compute clause without the by keyword.

Rules for compute clauses

Adaptive Server does not allow the distinct keyword with the row aggregates.
The columns in a compute clause must appear in the select list.
You cannot use select into (see Chapter 7, "Creating Databases and Tables") in the same statement as a compute clause because statements that include compute do not generate normal rows.
If you use compute with the by keyword, you must also use an order by clause. The columns listed after by must be identical to, or a subset of, those listed after order by, and must be in the same left-to-right order, start with the same expression, and not skip any expressions.
For example, suppose the order by clause is:
```
order by a, b, c 
```
The compute clause can be any or all of these:
```
compute row_aggregate (column_name) by a, b, c 
```
```
compute row_aggregate (column_name) by a, b 
```
```
compute row_aggregate (column_name) by a
```
The compute clause cannot be any of these:
```
compute row_aggregate (column_name) by b, c 
```
```
compute row_aggregate (column_name) by a, c
```
```
compute row_aggregate (column_name) by c
```
You must use a column name or an expression in the order by clause; you cannot sort by a column heading.
The compute keyword can be used without by to generate grand totals, grand counts, and so on. order by is optional if you use the compute keyword without by. The compute keyword without by is discussed under "Grand values: compute without by".


	Expand Search