Home

	Expand Search

Transact-SQL User's Guide

Chapter 3 Using Aggregates, Grouping, and Sorting

Using aggregate functions

Using aggregate functions

The aggregate functions are: sum, avg, count, min, max, and count(*). 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, type:

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 take 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, and count 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:

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

Aggregate functions and datatypes

You can use the aggregate functions with any type of column, with the following exceptions:

• You can use sum and avg with numeric columns only--int, smallint, tinyint, decimal, numeric, float, and money.

• You cannot use min and max with bit datatypes.

• You cannot use aggregate functions other than count(*) with text and image datatypes.

For example, you can use min (minimum) to find the lowest value--the one closest to the beginning of the alphabet--in a character type column:

select min(au_lname) 
from authors 

-------------------------- 
Bennet 
 
(1 row affected) 

However, you cannot average the contents of a text column:

select avg(au_lname) 
from authors 

-------------------------- 
Bennet 
 
(1 row affected) 

Msg 257, Level 16, State 1:

Line 1:

Implicit conversion from datatype 'VARCHAR' to 'INT' is not allowed.  Use the CONVERT function to run this query.

count vs. count (*)

While count finds the number of non-null values in the expression, count(*) finds the total number of rows in a table. This statement finds the total number of books:

select count(*) 
from titles 

------------------ 
                18 
 
(1 row affected) 

count(*) returns the number of rows in the specified table without eliminating duplicates. It counts each row, including those containing null values.

Like other aggregate functions, you can combine count(*) with other aggregates in the select list, with where clauses, and so on:

select count(*), avg(price) 
from titles 
where advance > 1000 

---------- --------- 
        15     14.42 
 
(1 row affected) 

Using aggregate functions with distinct

You can use the optional keyword distinct only with sum, avg, and count. When you use distinct, Adaptive Server eliminates duplicate values before performing calculations.

If you use distinct, you cannot include an arithmetic expression in the argument. The argument must use a column name only. distinct appears inside the parentheses and before the column name. For example, to find the number of different cities in which there are authors, enter:

select count(distinct city) 
from authors 

------------- 
           16
 
(1 row affected)

For an accurate calculation of the average price of all business books, omit distinct. The following statement returns the average price of all business books:

select avg(price) 
from titles 
where type = "business" 

------------- 
        13.73 
 
(1 row affected) 

However, if two or more books have the same price and you use distinct, the shared price is included only once in the calculation:

select avg(distinct price) 
from titles 
where type = "business" 

------------- 
        11.64 
 
(1 row affected)

Null values and the aggregate functions

Adaptive Server ignores any null values in the column on which the aggregate function is operating for the purposes of the function (except count(*), which includes them). If you have set ansinull to on, Adaptive Server returns an error message whenever a null value is ignored. For more information, see the set command in the Reference Manual.

For example, the count of advances in the titles table is not the same as the count of title names, because of the null values in the advance column:

select count(advance) 
from titles 

------------- 
           16 
 
(1 row affected)

select count(title) 
from titles 

------------- 
           18 
 
(1 row affected) 

If all the values in a column are null, count returns 0. If no rows meet the conditions specified in the where clause, count returns 0. The other functions all return NULL. Here are examples:

select count(distinct title) 
from titles 
where type = "poetry"

------------- 
            0 
 
(1 row affected) 

select avg(advance) 
from titles 
where type = "poetry" 

------------- 
         NULL 
 
 (1 row affected)