MySQL COUNT() and nulls

Question

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?

score 36 · Accepted Answer · edited Mar 16 '23 at 08:49

36

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.

edited Mar 16 '23 at 08:49

n00dl3

21,213
7
66
76

answered Aug 30 '09 at 15:07

Godeke

16,131
4
62
86

1

Is there any way to count nulls too, with COUNT(Expression)? – Jo Smo Apr 04 '14 at 11:02
If you do a COUNT( * ) then all rows are counted no matter what the contents of those rows. If you want to see both the total rows *and* the count of rows with values for SomeColumn, do a SELECT COUNT( * ) as AllRows, COUNT(SomeColumn) as SomeColumnCount. AllRows will be the total rows returned by the query, while SomeColumnCount will be the count of rows where SomeColumn had a value. – Godeke Apr 08 '14 at 04:57

score 21 · Answer 2 · answered Jun 30 '15 at 07:22

21

If you want to count NULLs as well, try

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

answered Jun 30 '15 at 07:22

Roey

1,647
21
16

Great addition @Roey – Phil Young Oct 13 '16 at 10:17

score 7 · Answer 3 · edited May 28 '20 at 09:21

7

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.

edited May 28 '20 at 09:21

Özgür Can Karagöz

1,039
1
13
32

answered Aug 30 '09 at 15:16

Sergei

2,747
1
17
16

"I don't see the point in the record with NULL values". oh it just came to my mind. now when u say this i figured u are right, didnt think abt it when i asked. – iceangel89 Aug 31 '09 at 14:16

score 3 · Answer 4 · answered Aug 30 '09 at 15:09

3

count(*) is not for non-null columns, it's just the way to ask to count all rows. Roughly equivalent to count(1).

answered Aug 30 '09 at 15:09

Michael Krelin - hacker

138,757
24
193
173

score 2 · Answer 5 · edited Apr 04 '19 at 20:27

2

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;

edited Apr 04 '19 at 20:27

Guillaume Racicot

39,621
9
77
141

answered Apr 04 '19 at 20:07

Mario Cespedes

21
1

score 1 · Answer 6 · answered Mar 19 '21 at 14:29

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.

score 0 · Answer 7 · answered Oct 27 '21 at 12:57

0

select count(*) as 'total', sum(if(columna is null, 1, 0)) as 'nulos' from tabla;

answered Oct 27 '21 at 12:57

edwin cristian castaeda huerta

1

1

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Oct 27 '21 at 13:27

MySQL COUNT() and nulls

7 Answers7

Linked