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(*) and count_big(*), which includes them). If you have set ansinull to on, Adaptive Server returns an error message whenever a null value is ignored. See the Reference Manual: Commands.

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)