Null Values and Aggregate Functions

Null values in a column are ignored while an aggregate function is operating.

For example, the count of advances in the titles table is not the same as the count of title names, because null values in the advance column are not counted.

USE pubs

SELECT COUNT(advance)    

FROM titles

  

Here is the result set:

------------    

16    

    

(1 row(s) affected)    

    

    

USE pubs

SELECT COUNT(title)    

FROM titles    

  

Here is the result set:

------------    

18    

    

(1 row(s) affected)    

  

If no rows meet the condition(s) specified in the WHERE clause, COUNT returns a value of zero. The other functions all return NULL. Here are two examples:

USE pubs

SELECT COUNT(DISTINCT title)    

FROM titles    

WHERE type = 'poetry'

  

Here is the result set:

------------    

0    

    

(1 row(s) affected)    

    

    

USE pubs

SELECT AVG(advance)    

FROM titles    

WHERE type = 'poetry'    

  

Here is the result set:

------------    

(null)    

    

(1 row(s) affected)    

  

COUNT(*), however, counts each row, even if all column values are NULL.

  


(c) 1988-98 Microsoft Corporation. All Rights Reserved.