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.

Example 1

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)

Example 2

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.

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

Example 3

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 (Example 1 and Example 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.