SQL Count NULL values is 0 even when there are NULL values

Question

In a particular case I have a database table populated with 1070 items, and at some point in the process I add a column called 'Current_Status' to it. As a result, all items have a new field that is initially NULL.

This table is used as a queue, and for each row that is processed, I update the 'Current_Status' field to 'Processed' or 'Non processed'.

In order to see how is the process going, I was counting the remaining items that still had the status NULL using this query:

SELECT COUNT([Current_Status]) FROM Table_Name WHERE [Current_Status] IS NULL

The issue is that after the first 1000 items, the result for that query execution was 0, even if I checked and using a SELECT * FROM Table_Name query shown that there where still some rows with status NULL.

Any ideas what might be causing this?

I checked this situation using Azure Data Studio 1.4.5.

score 7 · Accepted Answer · answered Mar 15 '19 at 09:13

7

The reason for that is because you have provided count with a column value which is null. Instead, use count(*):

SELECT COUNT(*) FROM Table_Name WHERE [Current_Status] IS NULL

Sample data:

current_status
--------------
Processed
Null
Not Processed
Null

And the difference between two queries:

count(current_status)

SELECT count(current_status) FROM table_name WHERE current_status IS NULL 

0

count(*)

SELECT count(*) FROM table_name WHERE current_status IS NULL 

2

answered Mar 15 '19 at 09:13

Kamil Gosciminski

16,547
8
49
72

But the problem is coming up when there are values in the column distinct than null (almost all of them). Using '*' instead would solve the issue then? – Pedro Zampella Mar 15 '19 at 09:19

score 4 · Answer 2 · answered Mar 15 '19 at 09:19

With

SELECT COUNT([Current_Status]) FROM Table_Name WHERE [Current_Status] IS NULL

you say "take all rows where the current_status is null and count how many of these current_status are not null". Which is zero of course. COUNT(<expression>) counts non-null occurrences of the expression.

You want to count rows instead:

SELECT COUNT(*) FROM table_name WHERE current_status IS NULL;

Or count remains:

SELECT COUNT(*) - COUNT(current_status) FROM table_name;

SQL Count NULL values is 0 even when there are NULL values

2 Answers2