count(null)

Question

count(null)

Raphra

Mr or Mrs. 500

Points: 522
More actions
July 12, 2016 at 12:54 pm

#328196

I was reading this article[/url], and I was surprised to note that [font="Courier New"]select count(convert(int,null))[/font] returns 0 while [font="Courier New"]select count(null)[/font] errors out.
I have found that [font="Courier New"]select count(convert(varchar,null))[/font] returns 0 as well.
Can anyone explain why?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

drew.allen SSC Guru Points: 76944 More actions · Answer 1

Because you haven't specified a data type for the value NULL, and it can't figure it out from the value itself, so it assigns a NULL data type. It's a little confusing, because both the value and the data type are NULL, but the message is clearly referring to the data type as can be seen by the following:

SELECT COUNT(CAST(NULL AS TEXT))

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Raphra Mr or Mrs. 500 Points: 522 More actions · Answer 2

I did not pay closer attention to the error message and it makes sense now. Yes, it is confusing that NULL is the datatype of NULL.

Thanks for the example too.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

Your turn, please. 😉

Why would you ever need to do a SELECT COUNT(NULL)?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)