Why does the count() function require a parameter?

Question

The count() function counts rows, and is very often used as count(*). If I had a table with phoneNo and firstName, count(phoneNumber) returns the same number as count(*). All columns are always present, if not populated, in each row.

Answer 1

There is a subtle difference.

If you specify a field , count(fieldname) will count records which have non null values for the field. count(*) will give you total rows.

Answer 2

See MSDN

Count(phoneNo) will return the number of records where phoneNo is not null. Count(*) returns the number or rows regardless.

Answer 3

COUNT(*) will count every row. COUNT(yourColumn) won't include rows where yourColumn is NULL.

For example, if there are 20 rows in your table and the phoneNumber column is NULL in 5 of those rows, then COUNT(*) will return 20 whereas COUNT(phoneNumber) will return 15.

Answer 4

On the other hand you can count distinct values. count(distinct firstName) may deliver a different result as count(distinct phoneNumber) and both may be different to count().