# compute Clause

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:
• sum – is the total of values in the (numeric) column.

• avg – is the average of values in the (numeric) column.

• min – is the lowest value in the column.

• max – is the highest value in the column.

• count – is the number of values in the column as an integer.

• count – is the number of values in the column as a bigint.

• column_name – is the name of a column, which must be enclosed in parentheses. You can use numeric columns only with sum and avg. You can only use integer, numeric, and decimal columns with sum and avg.
• 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 cookbook 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 cookbook that costs more than \$12, with one compute clause applying several aggregate functions to the same set of grouping columns:
```select type, price, advance
from titles
where price > \$12
and type like "%cook"
order by type, price
compute sum (price), sum (advance) by type

---------  ---------  ------------
mod_cook       19.99          0.00
sum         sum
---------  ------------
19.99          0.00

---------  ---------  ------------
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, with one compute clause applying several aggregate functions to the same set of grouping columns:
```select type, price, advance
from titles
where price > \$12
and type like "%cook"
order by type, price
--------- --------- -------------
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, using more than one compute clause to create more than one group:
```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"

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

You can use compute without by to generate grand totals, grand counts, and so on. order by is optional if you use the compute keyword without by.

• 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.

• You can use compute without by to generate grand totals, grand counts, and so on. order by is optional if you use the compute keyword without by.

• 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 cannot 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
• compute by a, b

See also avg, count, max, min, sum in Reference Manual: Building Block.

## Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Related reference
group by and having Clauses
select