MySQL COUNT() and nulls

If you want to count NULLs as well, try

SELECT COUNT(IFNULL(col, 1)) FROM table;

Correct. COUNT(*) is all rows in the table, COUNT(Expression) is where the expression is non-null only.

If all columns are NULL (which indicates you don't have a primary key, so this shouldn't happen in a normalized database) COUNT(*) still returns all of the rows inserted. Just don't do that.

You can think of the * symbol as meaning "in the table" and not "in any column".

This is covered in the MySQL Reference Manual.


just checked:

select count(*)

returns 1 with one record filled with NULLs

select count(field)

returns 0.

I don't see the point in the record with NULL values. Such record must not exist.

Tags:

Mysql