Chapter 3: Using Aggregates, Grouping, and Sorting

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—bigint, int, smallint, tinyint, unsigned bigint, unsigned int, unsigned smallint, decimal, numeric, float, and money.
You cannot use min and max with bit datatypes.
You cannot use aggregate functions other than count(*) and count_big(*) 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

Msg 257, Level 16, State 1:
-------------------------- 
(1 row affected) 
Line 1:
Implicit conversion from datatype ’VARCHAR’ to ’INT’ is not allowed.  Use the CONVERT function to run this query.

View this book as PDF