Any null values in the column on which the aggregate function is operating are ignored for the purposes of the function.
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:
SELECT COUNT(advance) |
|
FROM titles |
|
------------ |
|
16 |
|
(1 row(s) affected) |
|
SELECT COUNT(title) |
|
FROM titles |
|
------------ |
|
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 examples:
SELECT COUNT(DISTINCT title) |
|
FROM titles |
|
WHERE type = 'poetry' |
|
------------ |
|
0 |
|
(1 row(s) affected) |
|
SELECT AVG(advance) |
|
FROM titles |
|
WHERE type = 'poetry' |
|
------------ |
|
(null) |
|
(1 row(s) affected) |
|
COUNT(*), however, counts each row, even if every column in it is null.