Reference Manual

Home

Reference Manual

Chapter 2: Transact-SQL Functions

Aggregate Functions

The aggregate functions generate summary values that appear as new columns in the query results. The aggregate functions are:

avg
count
max
min
sum

Aggregate functions can be used in the select list or the having clause of a select statement or subquery. They cannot be used in a where clause.

Each aggregate in a query requires its own worktable. Therefore, a query using aggregates cannot exceed the maximum number of worktables allowed in a query (12).

When an aggregate function is applied to a char datatype value, it implicitly converts the value to varchar, stripping all trailing blanks.

Aggregates Used with group by

Aggregates are often used with group by. With group by, the table is divided into groups. Aggregates produce a single value for each group. Without group by, an aggregate function in the select list produces a single value as a result, whether it is operating on all the rows in a table or on a subset of rows defined by a where clause.

Aggregate Functions and NULL Values

Aggregate functions calculate the summary values of the non-null values in a particular column. If the ansinull option is set off (the default), there is no warning when an aggregate function encounters a null. If ansinull is set on, a query returns the following SQLSTATE warning when an aggregate function encounters a null:

Warning- null value eliminated in set function

Vector and Scalar Aggregates

Aggregate functions can be applied to all the rows in a table, in which case they produce a single value, a scalar aggregate. They can also be applied to all the rows that have the same value in a specified column or expression (using the group by and, optionally, the having clause), in which case, they produce a value for each group, a vector aggregate. The results of the aggregate functions are shown as new columns.

You can nest a vector aggregate inside a scalar aggregate. For example:

select type, avg(price), avg(avg(price)) 
from titles 
group by type

type 
------------  ------------  ------------ 
UNDECIDED             NULL        15.23  
business             13.73        15.23 
mod_cook             11.49        15.23 
popular_comp         21.48        15.23 
psychology           13.50        15.23 
trad_cook            15.96        15.23 

(6 rows affected)

The group by clause applies to the vector aggregatežin this case, avg(price). The scalar aggregate, avg(avg(price)), is the average of the average prices by type in the titles table.

In standard SQL, when a select_list includes an aggregate, all the select_list columns must either have aggregate functions applied to them or be in the group by list. Transact-SQL has no such restrictions.

Example 1 shows a select statement with the standard restrictions. Example 2 shows the same statement with another item (title_id) added to the select list. order by is also added to illustrate the difference in displays. These "extra" columns can also be referenced in a having clause.

select type, avg(price), avg(advance)
from titles
group by type

type 
------------  ------------  ------------ 
UNDECIDED             NULL         NULL 
business             13.73     6,281.25 
mod_cook             11.49     7,500.00 
popular_comp         21.48     7,500.00 
psychology           13.50     4,255.00 
trad_cook            15.96     6,333.33 
 
(6 rows affected)

select type, title_id, avg(price), avg(advance)
from titles
group by type
order by type

type         title_id 
-----------  --------    ---------- --------- 
UNDECIDED       MC3026      NULL        NULL 
business        BU1032      13.73   6,281.25 
business        BU1111      13.73   6,281.25 
business        BU2075      13.73   6,281.25 
business        BU7832      13.73   6,281.25 
mod_cook        MC2222      11.49   7,500.00 
mod_cook        MC3021      11.49   7,500.00 
popular_comp    PC1035      21.48   7,500.00 
popular_comp    PC8888      21.48   7,500.00 
popular_comp    PC9999      21.48   7,500.00 
psychology      PS1372      13.50   4,255.00 
psychology      PS2091      13.50   4,255.00 
psychology      PS2106      13.50   4,255.00 
psychology      PS3333      13.50   4,255.00 
psychology      PS7777      13.50   4,255.00 
trad_cook       TC3218      15.96   6,333.33 
trad_cook       TC4203      15.96   6,333.33 
trad_cook       TC7777      15.96   6,333.33

You can use either a column name or any other expression (except a column heading or alias) after group by.

Null values in the group by column are put into a single group.

The compute clause in a select statement uses row aggregates to produce summary values. The row aggregates make it possible to retrieve detail and summary rows with one command. Example 3 illustrates this feature:

select type, title_id, price, advance
from titles
where type = "psychology"
order by type
compute sum(price), sum(advance) by type

type        title_id    price       advance 
----------- -------     ----------  ---------
psychology  PS1372          21.59   7,000.00 
psychology  PS2091          10.95   2,275.00 
psychology  PS2106          7.00    6,000.00 
psychology  PS3333          19.99   2,000.00 
psychology  PS7777          7.99    4,000.00 
                            sum     sum 
                            ------- ---------- 
                            67.52   21,275.00

Note the difference in display between example 3 and the examples without compute (examples 1 and 2).

Aggregate functions cannot be used on virtual tables such as sysprocesses and syslocks.

If you include an aggregate function in the select clause of a cursor, that cursor cannot be updated.

Aggregate Functions As Row Aggregates

Row aggregate functions generate summary values that appear as additional rows in the query results.

To use the aggregate functions as row aggregates, use the following syntax:

Start of select statement

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

where:

column_name is the name of a column. It must be enclosed in parentheses. Only exact numeric, approximate numeric, and money columns can be used with sum and avg.
One compute clause can apply the same function to several columns. When using more than one function, use more than one compute clause.
by indicates that row aggregate values are to be calculated 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.

The row aggregates make it possible to retrieve detail and summary rows with one command. The aggregate functions, on the other hand, 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.

The following examples illustrate the differences:

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

type 
----------  ----------  ---------------- 
mod_cook         22.98         15,000.00 
trad_cook        47.89         19,000.00 
 
(2 rows affected)

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

type        price       advance 
----------  ----------  ---------------- 
mod_cook          2.99         15,000.00 
mod_cook         19.99              0.00 
            sum         sum 
            ----------  ---------------- 
                 22.98         15,000.00 
type         price         advance 
----------  ----------  ---------------- 
trad_cook        11.95          4,000.00 
trad_cook        14.99          8,000.00 
trad_cook        20.95          7,000.00 
            sum         sum 
            ----------  ---------------- 
                 47.89         19,000.00 
(7 rows affected)

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

Compute Result: 
---------------------- -----------------
                 22.98         15,000.00 
type         price         advance 
----------  ----------  ---------------- 
trad_cook        11.95          4,000.00 
trad_cook        14.99          8,000.00 
trad_cook        20.95          7,000.00 

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

The columns in the compute clause must appear in the select list.

If the ansinull option is set off (the default), there is no warning when a row aggregate encounters a null. If ansinull is set on, a query returns the following SQLSTATE warning when a row aggregate encounters a null:

Warning- null value eliminated in set function

You cannot use select into in the same statement as a compute clause because statements that include compute generate tables that include the summary results, which are not stored in the database.


	Expand Search