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