Chapter 3: Using Aggregates, Grouping, and Sorting

Using aggregate functions

The aggregate functions are: sum, avg, count, min, max, count_big, count(*), and count_big(*). You can use aggregate functions to calculate and summarize data. For example, to find out how many books have been sold in the titles table of the pubs2 database, enter:

select sum(total_sales)
from titles

------------- 
       97746 
 
(1 row affected)

Note that there is no column heading for the aggregate column in the example.

An aggregate function takes as an argument the column name on whose values it will operate. You can apply aggregate functions to all the rows in a table, to a subset of the table specified by a where clause, or to one or more groups of rows in the table. From each set of rows to which an aggregate function is applied, Adaptive Server generates a single value.

Here is the syntax of the aggregate function:

aggregate_function ( [all | distinct] expression)

expression is usually a column name. However, it can also be a constant, a function, or any combination of column names, constants, and functions connected by arithmetic or bitwise operators. You can also use a case expression or subquery in an expression.

For example, with this statement, you can calculate the average price of all books if prices were doubled:

select avg(price * 2) 
from titles

------------- 
        29.53 
 
(1 row affected)

You can use the optional keyword distinct with sum, avg, count, min, and max to eliminate duplicate values before the aggregate function is applied. all, which performs the operation on all rows, is the default.

The syntax of the aggregate functions and the results they produce are shown in Table 3-1:

**Table 3-1: Syntax and results of aggregate functions**
Aggregate Function	Result
*sum([all \| distinct] expression)*	Total of the (distinct) values in the expression
avg([all \| distinct] expression	Average of the (distinct) values in the expression
*count([all \| distinct] expression)*	Number of (distinct) non-null values in the expression returned as an integer.
count_big [all \| distinct] expression	Number of (distinct) non-null values in the expression returned as a bigint..
*count()**	Number of selected rows as an integer.
*count_big()**	Number of selected rows as a bigint.
*max(expression)*	Highest value in the expression
*min(expression)*	Lowest value in the expression

You can use the aggregate functions in a select list, as shown in the previous example, or in the having clause. For information about the having clause, see “Selecting groups of data: the having clause”.

You cannot use aggregate functions in a where clause, but most select statements with an aggregate function in the select list include a where clause that restricts the rows to which the aggregate is applied. In the examples given earlier in this section, each aggregate function produced a single summary value for the entire table.

If a select statement includes a where clause, but not a group by clause (see “Organizing query results into groups: the group by clause”), an aggregate function produces a single value for the subset of rows, called a scalar aggregate. However, a select statement can also include a column in its select list (a Transact-SQL extension), that repeats the single value for each row in the result table.

This query returns the average advance and the sum of sales for business books only, and has a column name preceding it called “advance and sales”:

select "advance and sales", avg(advance), sum(total_sales)
from titles 
where type = "business"

-----------------  ----------------- ----------- 
advance and sales          6,281.25       30788  

(1 row affected)

View this book as PDF