# compute clause

### Description

Generates summary values that appear as additional rows in the query results.

### Syntax

```start_of_select_statement
compute row_aggregate (column_name)
[, row_aggregate(column_name)]...
[by column_name [, column_name]...]
```

### Parameters

row_aggregate

is one of the following:

Function

Meaning

sum

Total of values in the (numeric) column

avg

Average of values in the (numeric) column

min

Lowest value in the column

max

Highest value in the column

count

Number of values in the column

column_name

is the name of a column. It must be enclosed in parentheses. Only numeric columns can be used with sum and avg.

One compute clause can apply several aggregate functions to the same set of grouping columns (see Examples 2 and 3). To create more than one group, use more than one compute clause (see Example 5).

by

calculates the row aggregate values for subgroups. Whenever the value of the by item changes, row aggregate values are generated. If you use by, you must use order by.

Listing more than one item after by breaks a group into subgroups and applies a function at each level of grouping.

### Examples

#### Example 1

Calculates the sum of the prices of each type of cook book that costs more than \$12:

```select type, price
from titles
where price > \$12
and type like "%cook"
order by type, price
compute sum(price) by type
```

```type       price
---------  ------------
mod_cook          19.99
sum
------------
19.99
type       price
---------  ------------
sum
------------
35.94
(5 rows affected)
```

#### Example 2

Calculates the sum of the prices and advances for each type of cook book that costs more than \$12:

```select type, price, advance
from titles
where price > \$12
and type like "%cook"
order by type, price
```

```type       price      advance
---------  ---------  ------------
mod_cook       19.99          0.00
sum         sum
---------  ------------
19.99          0.00
```

```type       price      advance
---------  ---------  ------------
sum        sum
---------  ------------
35.94     15,000.00
(5 rows affected)
```

#### Example 3

Calculates the sum of the prices and maximum advances of each type of cook book that costs more than \$12:

```select type, price, advance
from titles
where price > \$12
and type like "%cook"
order by type, price
```

```type      price     advance
--------- --------- -------------
mod_cook      19.99         0.00
sum
---------
19.99
max
-------------
0.00
```

```type      price     advance
--------- --------- -------------
sum
---------
35.94
max
-------------
8,000.00
(5 rows affected)
```

#### Example 4

Breaks on type and pub_id and calculates the sum of the prices of psychology books by a combination of type and publisher ID:

```select type, pub_id, price
from titles
where price > \$10
and type = "psychology"
order by type, pub_id, price
compute  sum(price) by type, pub_id
```

```type         pub_id    price
------------ --------- -----------
psychology   0736          10.95
psychology   0736          19.99
sum
---------
30.94
```

```type         pub_id    price
------------ --------- ---------
psychology   0877          21.59
sum
---------
21.59
(5 rows affected)
```

#### Example 5

Calculates the grand total of the prices of psychology books that cost more than \$10 in addition to calculating sums by type and pub_id:

```select type, pub_id, price
from titles
where price > \$10
and type = "psychology"
order by type, pub_id, price
compute  sum(price) by type, pub_id
compute  sum(price) by type
```

```type         pub_id    price
------------ --------- ---------
psychology   0736          10.95
psychology   0736          19.99
sum
---------
30.94
```

```type         pub_id    price
------------ --------- ---------
psychology   0877          21.59
sum
---------
21.59
sum
---------
52.53
(6 rows affected)
```

#### Example 6

Calculates the grand totals of the prices and advances of cook books that cost more than \$10:

```select type, price, advance
from titles
where price > \$10
and type like "%cook"
```

```type       price        advance
---------  -----------  --------------
mod_cook         19.99            0.00
sum         sum
-----------  --------------
67.88       19,000.00
(5 rows affected)
```

#### Example 7

Calculates the sum of the price of cook books and the sum of the price used in an expression:

```select type, price, price*2
from titles
where type like "%cook"
compute sum(price), sum(price*2)
```

```type         price
------------ --------------  ------------
mod_cook              19.99         39.98
mod_cook               2.99          5.98
sum             sum
=============   ============
70.87        141.74
```

### Usage

• The compute clause allows you to see the detail and summary rows in one set of results. You can calculate summary values for subgroups, and you can calculate more than one aggregate for the same group.

• compute 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. See Example 6.

• If you use compute by, you must also use an order by clause. The columns listed after compute 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, if the order by clause is``` order by a, b, c```, the compute by clause can be any (or all) of these:

```compute by a, b, c
compute by a, b
compute by a
```

#### Restrictions

• You cannot use more than 127 aggregate columns in a compute clause.

• You cannot use a compute clause in a cursor declaration.

• Summary values can be computed for both expressions and columns. Any expression or column that appears in the compute clause must appear in the select list.

• Aliases for column names are not allowed as arguments to the row aggregate in a compute clause, although they can be used in the select list, the order by clause, and the by clause of compute.

• You cannot use select into in the same statement as a compute clause, because statements that include compute do not generate normal tables.

• If a compute clause includes a group by clause:

• The compute clause cannot contain more than 255 aggregates

• The group by clause cannot contain more than 255 columns

• Columns included in a compute clause cannot be longer than 255 bytes.

#### compute results appear as a new row or rows

• The aggregate functions ordinarily produce a single value for all the selected rows in the table or for each group, and these summary values are shown as new columns. For example:

```select type, sum(price), sum(advance)
from titles
where type like "%cook"
group by type
```

```type
-------------  ---------  ----------
mod_cook           22.98  15,000.00

(2 rows affected)
```

• The compute clause makes it possible to retrieve detail and summary rows with one command. For example:

```select type, price, advance
from titles
where type like "%cook"
order by type
```

```type        price       advance
----------  ----------  ----------------
mod_cook          2.99         15,000.00
mod_cook         19.99              0.00

Compute Result:
---------------------- -----------------
22.98         15,000.00
----------  ----------  ----------------

Compute Result:
---------------------- -----------------
47.89         19,000.00
(7 rows affected)
```

• Table 7-4 lists the output and grouping of different types of compute clauses.

Table 7-4: compute by clauses and detail rows

Clauses and grouping

Output

Examples

One compute clause, same function

One detail row

1, 2, 4, 6, 7

One compute clause, different functions

One detail row per type of function

3

More than one compute clause, same grouping columns

One detail row per compute clause; detail rows together in the output

Same results as having one compute clause with different functions

More than one compute clause, different grouping columns

One detail row per compute clause; detail rows in different places, depending on the grouping

5

#### Case sensitivity

• If your server has a case-insensitive sort order installed, compute ignores the case of the data in the columns you specify. For example, given this data:

```select * from groupdemo
```

```lname      amount
---------- ------------------
Smith                  10.00
smith                   5.00
SMITH                   7.00
Levi                    9.00
Lévi                   20.00
```

compute by on lname produces these results:

``` select lname, amount from groupdemo
order by lname
compute sum(amount) by lname
```

``` lname      amount
---------- ------------------------
Levi                           9.00

Compute Result:
------------------------
9.00

lname      amount
---------- ------------------------
Lévi                          20.00

Compute Result:
------------------------
20.00

lname      amount
---------- ------------------------
smith                          5.00
SMITH                          7.00
Smith                         10.00

Compute Result:
------------------------
22.00
```

The same query on a case- and accent-insensitive server produces these results:

``` lname      amount
---------- ------------------------
Levi                           9.00
Lévi                          20.00

Compute Result:
------------------------
29.00

lname      amount
---------- ------------------------
smith                          5.00
SMITH                          7.00
Smith                         10.00

Compute Result:
------------------------
22.00
```

### Standards

SQL92 – Compliance level: Transact-SQL extension.