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 
---------  ------------ 
trad_cook         14.99 
trad_cook         20.95 
           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 

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

type       price      advance 
---------  ---------  ------------ 
trad_cook      14.99      8,000.00 
trad_cook      20.95      7,000.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 
compute sum (price), max (advance) by type type      price     advance 
--------- --------- ------------- 
mod_cook      19.99         0.00 
          sum 
          --------- 
              19.99 
                    max 
                    ------------- 
                             0.00

type      price     advance 
--------- --------- ------------- 
trad_cook     14.99      8,000.00 
trad_cook     20.95      7,000.00 
          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" 
compute sum (price), sum (advance)

type       price        advance 
---------  -----------  -------------- 
mod_cook         19.99            0.00 
trad_cook        20.95        8,000.00 
trad_cook        11.95        4,000.00 
trad_cook        14.99        7,000.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
trad_cook             20.95         41.90
trad_cook             11.95         23.90
trad_cook             14.99         29.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.