THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Denis Gobo

Summer SQL Teaser #4 Nulls and Counts

First create this table

CREATE TABLE Teaser (ID int)
INSERT Teaser VALUES(1)
INSERT Teaser VALUES(2)
INSERT Teaser VALUES(1)
INSERT Teaser VALUES(2)
INSERT Teaser VALUES(NULL)


Without running this try to figure out what the result will be

SELECT COUNT(*),
COUNT(ID),
COUNT(DISTINCT ID)
FROM Teaser


For some more NULL summer fun you can read NULL Trouble In SQL Server Land
Published Friday, June 22, 2007 10:45 AM by Denis Gobo

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Peter W. DeBetta said:

That was too easy... :-)

My guess (that was correct) was - hmmm, that wouldn't be fun for anyone else reading this, so I'll give the base 2 representation of the ASCII values of my results:

110101, 110100, 110010

June 22, 2007 10:42 AM
 

Denis Gobo said:

Yeah it is not difficult....however a lot of people still trip up on the COUNT(ID)

Next week I will come up with something which is not as easy

June 22, 2007 10:50 AM
 

Adam Machanic said:

I was hoping for some twist with the DISTINCT :)

June 22, 2007 11:27 AM
 

Denis Gobo said:

yeah I tried to do DISTINCT * bit it didn't work  ;-(

June 22, 2007 11:34 AM
 

Bart said:

Next time create it as a #temp table or a @table variable, this way the code can just be copied and pasted to try out AND forgotten about.

June 24, 2007 2:35 PM
 

Denis Gobo said:

How about this one

SELECT COUNT(*),

COUNT(ID),

COUNT(DISTINCT ID) ,

COUNT(ALL ID)

FROM Teaser

WHERE ID >= ANY(SELECT 1)

AND ID <= ALL(SELECT 1)

AND ID <> SOME(SELECT 2)

I know I know I am being silly  ;-)

June 25, 2007 3:22 PM
 

steve smith said:

Interestingly enough, in SQL 7, the first value matches the second value, because the rules regarding nulls changed.

June 27, 2007 2:08 PM

Leave a Comment

(required) 
(required) 
Submit

About Denis Gobo

I was born in Croatia in 1970, when I was one I moved to Amsterdam (and yes Ajax is THE team in Holland) and finally in 1993 I came to the US. I have lived in New York City for a bunch of years and currently live in Princeton, New Jersey with my wife and 3 kids. I work for Dow Jones as a Database architect in the indexes department, one drawback: since our data goes back all the way to May 1896 I cannot use smalldates ;-( I have been working with SQL server since version 6.5 and compared to all the other bloggers here I am a n00b. Some of you might know me from http://sqlservercode.blogspot.com/ or even from some of the newsgroups where I go by the name Denis the SQL Menace If you are a Tek-Tips user then you might know me by the name SQLDenis, I am one of the guys answering SQL Questions in the SQL Programming forum.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement