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, 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)

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

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

ANSI SQL – Compliance level: Transact-SQL extension.

See also

Commands group by and having clauses, select

Functions avg, count, max, min, sum