Aggregate functions and datatypes

You can use the aggregate functions with any type of column, with these 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.