# Count Function Result (2)

• Comments posted to this topic are about the item Count Function Result (2)

--------------------------------------
;-)“Everything has beauty, but not everyone sees it.” ― Confucius

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

Igor Micev,My blog: www.igormicev.com

• This was removed by the editor as SPAM

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

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

• 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.

• 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.

• 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 (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.

• Ed Wagner (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!

• 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 ).

• Good question. Thanks for sharing Gary.

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

• 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?

• 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/

• Nice question, thanks.

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

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