COUNT() Function

  • Comments posted to this topic are about the item COUNT() Function

  • Thanks for the question

  • really good basic question Yogi πŸ™‚

    Thanks for it:-)

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

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

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

  • Nice question, thanks.

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

  • This was removed by the editor as SPAM

  • 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

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

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

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

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


    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/

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

  • Interesting, learned something, thanks for the good question....

  • Koen Verbeeck (4/16/2013)


    Nice question, thanks.

    +1

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

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

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