COUNT() Function

 Author Message yogyogi97 SSC-Enthusiastic Group: General Forum Members Points: 188 Visits: 152 Comments posted to this topic are about the item COUNT() Function mickyT Ten Centuries Group: General Forum Members Points: 1332 Visits: 3317 Thanks for the question kapil_kk SSCrazy Group: General Forum Members Points: 2591 Visits: 2764 really good basic question Yogi :-)Thanks for it:-) _______________________________________________________________To get quick answer follow this link:http://www.sqlservercentral.com/articles/Best+Practices/61537/ Danny Ocean UDP Broadcaster Group: General Forum Members Points: 1454 Visits: 1549 [b]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 :-) ThanksVinay Kumar-----------------------------------------------------------------Keep Learning - Keep Growing !!!www.GrowWithSql.com kapil_kk SSCrazy Group: General Forum Members Points: 2591 Visits: 2764 For the last query:select COUNT(convert(int,NULL)) from #tempIt 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/ Koen Verbeeck SSCoach Group: General Forum Members Points: 18957 Visits: 13250 Nice question, thanks. How to post forum questions.Need an answer? No, you need a question.What’s the deal with Excel & SSIS?My blog at SQLKover.MCSE Business Intelligence - Microsoft Data Platform MVP Stewart "Arturius" Campbell SSCertifiable Group: General Forum Members Points: 6414 Visits: 7194 Nice back-to-basics question, thanks ____________________________________________Space, the final frontier? not any more...All limits henceforth are self-imposed.“libera tute vulgaris ex” TomThomson SSChampion Group: General Forum Members Points: 11411 Visits: 12091 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 kapil_kk SSCrazy Group: General Forum Members Points: 2591 Visits: 2764 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/ manik_anu Old Hand Group: General Forum Members Points: 398 Visits: 302 kapil_kk (4/15/2013)For the last query:select COUNT(convert(int,NULL)) from #tempIt 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 ManikYou cannot get to the top by sitting on your bottom.