COUNT columns containing NULL values (MSSQL)

Databases
1

Hi all,

For some reason when I attempt to perform a COUNT function on a column to COUNT all the entries that contain a NULL value my result is always ‘0’. But when I perform a query to get only the results that contain a NULL value in this particular column I get return all the rows that contain NULL value.

Why does the COUNT function not pick up the NULL values? Does it not work with columns that contain NULL values?

Thanks

Tryst

2

Count function does pick up the NULL values properly.

Try this out in the Northwind database.

select count(*) as cnt from Employees where Region is NULL

will return the count of records where region is null.

3

count(*) counts rows and doesn’t even look at nulls

count(columnname) exludes nulls

4

Though as much, as my query results were looking inconsistent.

Thanks

Tryst

5

NULLs in aggregate functions are ignored; for example, if you’re adding up and averaging say, incomes, the person with a null income will be ignored. Well and good if his income was null; not so hot if some silly person forgot to enter the value! Note that there is a useful property of the count function - if you provide it with a constant (eg 1) as an argument, it counts all the rows in the table, but if you provide it with an expression, it looks for all column names within that expression, and then excludes rows containing NULL values for any one of those columns!