15

So I came across something the other day at work, trying to count how many null values after running an import.

So, I did:

select COUNT(columnname) from table 
WHERE ColumnName is null

Which didn't count the nulls...

Then I did,

select COUNT(*) from table 
WHERE ColumnName is null

Which gave me the count.

So, something that bugged me is why exactly this doesn't count the null values.

I have looked at this question (along with a good search around Google...): In SQL, what's the difference between count(column) and count(*)?, and whilst it tells me that COUNT(columnname) doesn't count nulls, I would like to know exactly why nulls aren't counted using this method?

Many Thanks, James.

James Hatton
  • 676
  • 2
  • 9
  • 28
  • Why? For one, because it has been defined this way in the SQL standard. The reasoning behind that requirements is given in Jeffrey's answer. –  Oct 14 '14 at 20:06
  • 1
    This should be tagged with the particular DBMS that was used, as behaviours can differ (probably not in this case, but this is general advice) – underscore_d Aug 03 '17 at 08:49
  • done, thanks underscore_d – James Hatton Aug 03 '17 at 09:06

7 Answers7

14

COUNT counts values, since null is not a value it does not get counted.

If you want to count all null values you could do something like this:

SELECT COUNT(ID) as NotNull, SUM(CASE WHEN ID IS NULL then 1 else 0 end) as NullCount
Jeffrey Wieder
  • 2,336
  • 1
  • 14
  • 12
6

Why aren't nulls counted in COUNT(columnname)?

COUNT(*)

will count all rows

COUNT(columnname)

will count all rows, except those rows where columnname IS NULL.

And what's the reason? It's just that the COUNT() function is designed to work this way: NULL values are treated differently from other values, because NULL can be considered as a placeholder for "unknown" values, so it is very common that you just want to count rows that have a real value and skip rows that don't have.

Counting the rows that don't have a value is less common, and SQL doesn't provide a function for it. But you can calculate it easily:

SELECT
  COUNT(*) As rows,
  COUNT(columnname) AS non_null_count,
  COUNT(*) - COUNT(columnname) AS null_count
FROM
  yourtable
fthiella
  • 48,073
  • 15
  • 90
  • 106
2

If you instead do count(1) you wont be affected by this the filter what to count in the condition.

Jonny Axelsson
  • 46
  • 3
  • This should be way up. Works great for simple group bys when you just want to count a total of items, including nulls. – Dan H Jul 28 '17 at 12:56
1

COUNT counts only real values...null is not a value. So:

COUNT(*) is used when you want to include the null-able values.

If you just want to count the number of non-null-able values, you would use COUNT(columnname)

Hard Tacos
  • 370
  • 1
  • 5
  • 19
0

I'm not sure if overhead is a concern but it occurs to me, why count(*)? You could simply count any field where you know for certain there will be data (such as the ID field) and add a WHERE (columnName IS NULL).

select count(ID) as NullCount from yourTable where columnName is null

R Loomas
  • 303
  • 1
  • 2
  • 11
0

As @Jeffrey Wieder mentioned in his answer, count counts actual values and as null are not values, it ignores them. But incase you want to count all rows for a column including the null values, you can do - 

Select count(column_name isnull) from table_name

This is in PostgreSQL. If anybody can shed more light on why this works, that'd be awesome. My understanding is that mentioning "is null" inside count makes it count even null values.. Please correct me if I'm wrong.

Siddharth Jain
  • 23
  • 4
0

Try this.

select COUNT(1) from table WHERE ColumnName is null

Raman Sharma
  • 11
  • 1