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:

  • 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. It must be enclosed in parentheses. You can only use numeric columns with sum and avg. You can only use integer, numeric, decimal columns with and 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 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 
---------  ------------ 
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 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), 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)

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


Restrictions


compute results appear as a new row or rows


Case sensitivity

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

See also

Commands group by and having clauses, select

Functions avg, count, max, min, sum