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)
