Am I correct in saying:
COUNT(expr) WHERE expr IS NOT *
Will count only non nulls?
Will COUNT(*) always count all rows? And What if all columns are null?
Answers:
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Method 1
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.
Method 2
If you want to count NULLs as well, try
SELECT COUNT(IFNULL(col, 1)) FROM table;
Method 3
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.
Method 4
count(*) is not for non-null columns, it’s just the way to ask to count all rows. Roughly equivalent to count(1).
Method 5
Using MySQL I found this simple way:
SELECT count(ifnull(col,1)) FROM table WHERE col IS NULL;
This way will not work:
SELECT count(col) FROM table WHERE col IS NULL;
Method 6
If you want to count only the nulls you can also use COUNT() with IF.
Example:
select count(*) as allRows, count(if(nullableField is null, 1, NULL)) as missing from myTable;
You can change the if condiditon to count what you actually want. So you can have multiple counts in one query.
Method 7
select count(*) as ‘total’, sum(if(columna is null, 1, 0)) as ‘nulos’ from tabla;
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0