Null Values

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. COUNT(*), counts each row, even if all column values are NULL. Here are 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)