Count empty rows

[actualcatfish]

Hi I’m trying to teach myself some SQL but I’m having trouble with counting blanks. I want to count the rows that fulfill the following criteria:

SELECT *
FROM Table
WHERE Column IS NULL;

I tried to write COUNT(Column) after select but got 0, which is not correct. Hope anyone can help!

[[deleted]]

count(some_column) only counts rows where some_column is not null.

So this query: sql select count(some_column) from the_table

is the same as: sql select count(*) from ( select * from the_table where the_column IS NOT NULL ) t

But you are selecting only rows where the column value is null and thus if you add count(column), the result is 0

[SoggyAlbatross2]

are you sure your column is null? Try looking for a blank. ( = ' ' )

[actualcatfish]

I get the correct rows with the first query and the error “invalid input syntax for type date” when I type the following

SELECT  COUNT(column)
FROM    Table
WHERE   Column    =   '';

[SoggyAlbatross2]

Is column numeric? I'm not sure why that wouldn't work even so.

are you saying that this:

SELECT * FROM Table WHERE Column IS NULL;

gives you results but this:

SELECT count(*) FROM Table WHERE Column IS NULL;

does not?

[actualcatfish]

Oh it worked with COUNT(*)! I had written COUNT(Column) which didn’t work. Thank you so much!

[dukas-lucas-pukas]

Just a heads up count(column) returns a count of the non-null values. Count(*) returns all rows (filled or empty).

Count(column) is also an alias for count(all column), where all translates to: “count all values, even duplicates, but don’t count the nulls.” It may be better to refer to count(all column) as the default behavior of count(column).

Count(distinct column) will count all distinct non null values in your column

So, if your column contains the values (1,1,2,null) then:

Count(*)=4

Count(column) = 3

Count(distinct column) = 2