Why does count(1/null) work but count(null/null) doesn't?

Question

I saw this example in a presentation lately. When you run a query like this:

SELECT COUNT(NULL)

You get the following error:

Operand data type NULL is invalid for count operator.

When you run this:

SELECT COUNT(1/NULL)

You get 0 as a result, even though the division itself yields NULL as a result. Same happens if you swap the 1 and NULL.

But if you do this:

SELECT COUNT(NULL/NULL)

You get the same error as the first query again (the division itself is legit, yields NULL).

Can anyone explain how sql server works to give these kind of results?

Answer 1

In SQL Server certain aggregate functions such as COUNT, MIN, MAX and functions such as DATEADD require a datatype. NULL, on its own, does not have one so this gives you an error:

SELECT COUNT(NULL) -- Operand data type NULL is invalid for count operator.

Likewise for:

SELECT COUNT(NULL/NULL)

For 1/NULL the datatype is INT and the value is NULL so this works:

SELECT COUNT(1/NULL) -- 0

Likewise for:

SELECT COUNT(CAST(NULL AS INT)) -- 0