Chapter 3: Using Aggregates, Grouping, and Sorting

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

Organizing query results into groups: the group by clause

View this book as PDF