April 15, 2013 at 9:08 pm
Comments posted to this topic are about the item COUNT() Function
April 15, 2013 at 9:35 pm
Thanks for the question
April 15, 2013 at 11:27 pm
really good basic question Yogi 🙂
Thanks for it:-)
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 15, 2013 at 11:31 pm
yogyogi97 (4/15/2013)
COUNT(convert(int,NULL)) - It will give count as 0 always. Don't know why it is giving 0 count however COUNT() function counts NULL values also.
Please go through below select statement. Here we are try to convert "Varchar" and "int" into "NULL".
And as per as your Explanation. Count () function counts only non-NULL values. That's why its give output as "0".
I think so. If anyone have some other aspect, please let us know. 🙂
select CONVERT(INT, NULL), COUNT (CONVERT(INT, NULL)), CONVERT(VARCHAR(111), NULL), COUNT (CONVERT(VARCHAR(111), NULL))
Thanks for question 🙂
Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
April 15, 2013 at 11:33 pm
For the last query:
select COUNT(convert(int,NULL)) from #temp
It will return 0 because when you execute the query:
SELECT CONVERT(int,NULL) it will return NULL and as the return type of COUNT is INT so NULL will implicitly
converted to '0'.
Hope it will clear to you now Yogi 🙂
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 16, 2013 at 1:28 am
Nice question, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 16, 2013 at 2:25 am
This was removed by the editor as SPAM
April 16, 2013 at 4:46 am
Nice question, but as only one list of values ended with 0 it was easy to get the answer without checking anything else, so much easier than it looked at first sight.
If there had been two options with 0 at the end it might have tested whether people knew that count(ALL, expression) and count(expression) meant the same, or that count(constant) was the same as count(*), but as it was the answer could be got without any of that.
Tom
April 16, 2013 at 4:59 am
L' Eomot Inversé (4/16/2013)
Nice question, but as only one list of values ended with 0 it was easy to get the answer without checking anything else, so much easier than it looked at first sight.If there had been two options with 0 at the end it might have tested whether people knew that count(ALL, expression) and count(expression) meant the same, or that count(constant) was the same as count(*), but as it was the answer could be got without any of that.
Right Tom, question will be lil bit tricky if there were two option with ending 0....
May be the percentage of no of incorrect will be more than current 😉
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 16, 2013 at 5:40 am
kapil_kk (4/15/2013)
For the last query:select COUNT(convert(int,NULL)) from #temp
It will return 0 because when you execute the query:
SELECT CONVERT(int,NULL) it will return NULL and as the return type of COUNT is INT so NULL will implicitly
converted to '0'.
Hope it will clear to you now Yogi 🙂
thanks kapil... nice question
Manik
You cannot get to the top by sitting on your bottom.
April 16, 2013 at 6:42 am
kapil_kk (4/15/2013)
For the last query:select COUNT(convert(int,NULL)) from #temp
It will return 0 because when you execute the query:
SELECT CONVERT(int,NULL) it will return NULL and as the return type of COUNT is INT so NULL will implicitly
converted to '0'.
Hope it will clear to you now Yogi 🙂
I wouldn't think it's an implicit conversion so much as there are 0 non-null rows, so count returns 0.
Great question Yogi, I really liked this one.
April 16, 2013 at 7:07 am
Good question, mediocre explanation - no errors, but complicating things more than needed.
There are three variations of COUNT:
* COUNT(*) - returns the number of rows, regardless of content.
* COUNT(expression) - evaluates expression for each row, and counts returns the number of non-NULL results. Both COUNT(columnname) and COUNT(1) are commonly used versions of this variation. The last one is too - it uses a complex expression that always returns NULL; COUNT returns the number of non-NULL results, which is 0.
* COUNT(DISTINCT expression) - as the previous one, but only the number of distinct values is returned. So if the same value is returned multiple times, it will be counted as 1. Rarely used in practise. The version in this question that used a constant expression is legal syntax, but will never be used in practise, as it will by definition return 1, except if the source of the query has no rows.
April 16, 2013 at 7:13 am
Hugo Kornelis (4/16/2013)
Good question, mediocre explanation - no errors, but complicating things more than needed.There are three variations of COUNT:
* COUNT(*) - returns the number of rows, regardless of content.
* COUNT(expression) - evaluates expression for each row, and counts returns the number of non-NULL results. Both COUNT(columnname) and COUNT(1) are commonly used versions of this variation. The last one is too - it uses a complex expression that always returns NULL; COUNT returns the number of non-NULL results, which is 0.
* COUNT(DISTINCT expression) - as the previous one, but only the number of distinct values is returned. So if the same value is returned multiple times, it will be counted as 1. Rarely used in practise. The version in this question that used a constant expression is legal syntax, but will never be used in practise, as it will by definition return 1, except if the source of the query has no rows.
Good explanation Hugo 🙂
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 16, 2013 at 7:15 am
Interesting, learned something, thanks for the good question....
April 16, 2013 at 7:19 am
Viewing 15 posts - 1 through 15 (of 46 total)
You must be logged in to reply to this topic. Login to reply