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


NB! Dates updated: After a 1.5 year break, this year’s only Advanced Oracle Troubleshooting training class (updated with Oracle 12c content) takes place on 14-18 December 2015 and 11-15 January 2016 (I had to reschedule the start from November to December). So sign up now if you want to learn new cool stuff!

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 *