Select COUNT(*) and COUNT(column) are different things!

Every now and then I see someone wondering why Oracle is “returning wrong results” for some count queries when counting using COUNT(column_name) instead of COUNT(*) or COUNT(<constant>).

Oracle is actually returning correct results, its just that sometimes the people asking the questions haven’t realized that COUNT(column) is something semantically different from COUNT(*).

COUNT(*) operation counts all rows fed to it by execution plan branch under it.

COUNT(column) operation on the other hand counts all non-null values in that column from rows fed to it by execution plan branch under it.

And here’s a little example:

SQL> select count(*) from v$session;


SQL> select count(username) from v$session;

              1  <<-- only one non-null value in that column

SQL> select count(nvl(username,'blah')) from v$session; <<-- lets replace NULLs with non-nulls

This entry was posted in Oracle and tagged , . Bookmark the permalink.

12 Responses to Select COUNT(*) and COUNT(column) are different things!

  1. radino says:

    good point :)

    I like (and use) this behaviour especially with conjunction with CASE expresions:

    count(case col when 1 then 1 else null end),
    count(case col when 2 then 1 else null end)
    from tab

  2. Tanel Poder says:

    Yes, this can help writing pivot queries etc…

  3. pdv says:

    Nice reminder Tanel.
    Nulls are always good for a smile, and sometimes they bite you right where it hurts.

    I remeber “surely-a-bug-in-your-database” arguments with the equivalent of this:
    select count (:bindvar) from table

    The original was hidden in a subquery, and the overall-result was occasionally very “wrong”. And “it has always worked correctly”.

    During the original coding, nobody had counted/tested on :bindvar being NULL.

    (I have an old blog-rant on it somewhere – too lazy to link it up)

  4. Prem says:

    Thanks Tanel for explaining this.
    I have been thinking that all 3
    return the same results.

    How does “count(1)” differ from other 2 “count” ?

  5. Tanel Poder says:

    Count() is just like COUNT(*) … (as long as the constant is not NULL :)

  6. Achshar says:

    thanks i was about to use count(*) but then thought it is good to do some research before using it… and i got here..

  7. Badminton says:

    I was reading someone’s documentation and saw select count(-1) from table. I ran that in oracle and it seems to work similar to count(1). Any idea what the difference is?

  8. Tanel Poder says:

    Maybe the developer just wanted to get the negative count of rows from the table and used the above syntax instead of “select -count(*) from table”

  9. supriya says:

    thank you

  10. Sruthi says:

    Thank you Tanel.

  11. arun sharma says:

    hello sir i m trying to count the total value from a perticular column how i execute this query

Leave a Reply

Your email address will not be published. Required fields are marked *