Count Function Result (2)

Question

Post reply

Count Function Result (2)

chgn01

SSCarpal Tunnel

Points: 4295
More actions
January 23, 2016 at 3:33 pm

#399616

Comments posted to this topic are about the item Count Function Result (2)
--------------------------------------
;-)“Everything has beauty, but not everyone sees it.” ― Confucius
January 24, 2016 at 10:50 pm

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 15 total)

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

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 1

Good coverage of the count function with two QotDs. Thanks.

Igor Micev,My blog: www.igormicev.com

Toreador SSChampion Points: 11358 More actions · Answer 2

Again, a good idea, but the choices available meant I only had to think about the first two before I got a unique option.

TomThomson SSC Guru Points: 104773 More actions · Answer 3

Toreador (1/25/2016)
Again, a good idea, but the choices available meant I only had to think about the first two before I got a unique option.

Yes, if you know how COUNT works you don't have to check all the numbers.

But you don't have to know much about COUNT to get the answer. The where clause means you only have two rows to look at: (140, NULL) and (150,NULL). So any answer containing 3 is wrong. There's no imaginable way any count function could return for 1 for COUNT('Aim') when there are only thse two rows. So that leaves only two possible answers, and just knowing that that COUNT(column) doesn't count nulls tells you which one is right. So all you have to know about COUNT to get this answer is that COUNT(column) doesn't count NULLs.

Tom

Iulian -207023 SSCertifiable Points: 7639 More actions · Answer 4

OK, so we have :

COUNT( constant ) is the number of records returned by the query

COUNT( DISTINCT ... ) is self explanatory

COUNT( ALL ... ) is the default and is opposed to DISTINCT

COUNT( NULL ) is zero; although when I query: SELECT COUNT( NULL ) it returns the error:

Msg 8117, Level 16, State 1, Line 42

Operand data type NULL is invalid for count operator.

Any idea why this ?

T.a.

Ed Wagner SSC Guru Points: 287024 More actions · Answer 5

Iulian -207023 (1/25/2016)
OK, so we have :
COUNT( constant ) is the number of records returned by the query
COUNT( DISTINCT ... ) is self explanatory
COUNT( ALL ... ) is the default and is opposed to DISTINCT
COUNT( NULL ) is zero; although when I query: SELECT COUNT( NULL ) it returns the error:
Msg 8117, Level 16, State 1, Line 42
Operand data type NULL is invalid for count operator.
Any idea why this ?
T.a.

Interesting. I half-expected this to be covered in Books Online, but it isn't. My guess is that it's because NULL is unknown. If you wanted to count the nulls, you'd need to include a predicate in the WHERE clause.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Toreador SSChampion Points: 11358 More actions · Answer 6

Ed Wagner (1/25/2016)[/b]

Interesting. I half-expected this to be covered in Books Online, but it isn't. My guess is that it's because NULL is unknown. If you wanted to count the nulls, you'd need to include a predicate in the WHERE clause.

It's complaining about the data type rather than the value. This works (returns 0)

SELECT COUNT( CAST(NULL AS INT))

Ed Wagner SSC Guru Points: 287024 More actions · Answer 7

Toreador (1/25/2016)
Ed Wagner (1/25/2016)[/b]
Interesting. I half-expected this to be covered in Books Online, but it isn't. My guess is that it's because NULL is unknown. If you wanted to count the nulls, you'd need to include a predicate in the WHERE clause.
It's complaining about the data type rather than the value. This works (returns 0)
SELECT COUNT( CAST(NULL AS INT))

Right - it's unknown.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Revenant SSC-Forever Points: 42467 More actions · Answer 8

Ed Wagner (1/25/2016)
Toreador (1/25/2016)
Ed Wagner (1/25/2016)[/b]
Interesting. I half-expected this to be covered in Books Online, but it isn't. My guess is that it's because NULL is unknown. If you wanted to count the nulls, you'd need to include a predicate in the WHERE clause.
It's complaining about the data type rather than the value. This works (returns 0)
SELECT COUNT( CAST(NULL AS INT))
Right - it's unknown.

Or not applicable.

Gary, thanks for an interesting QotD!

patricklambin SSCrazy Eights Points: 9994 More actions · Answer 9

Thanks Gary for this nice QOD even if I have hesitated during 15 minutes because I detected the 3rd choice as the good one but I was thinking of a trap as it was too evident ( according to me ).

Umer Akram SSC-Insane Points: 22209 More actions · Answer 10

Good question. Thanks for sharing Gary.

Because Count(-100) and Count(1) are the same thing doesn't it. 🙂

Stephen Grimshaw Right there with Babe Points: 738 More actions · Answer 11

I'm a bit surpised at the behaviour when a number appears in the brackets. Wasn't it once the case that a number would have been interpreted as colid?

Hugo Kornelis SSC Guru Points: 64780 More actions · Answer 12

Stephen Grimshaw (1/26/2016)
I'm a bit surpised at the behaviour when a number appears in the brackets. Wasn't it once the case that a number would have been interpreted as colid?

No, that has never been the case.

I think you are confused by the (supported but not recommended) syntax of ORDER BY where a positive integer is interpreted as a column ordinal.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 13

Nice question, thanks.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP