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;

 COUNT(*)
----------
        23

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

COUNT(USERNAME)
---------------
              1  <<-- only one non-null value in that column

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

COUNT(NVL(USERNAME,'BLAH'))
---------------------------
                         23
This entry was posted in Oracle and tagged , . Bookmark the permalink.

10 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:

    select
    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:

    @Badminton
    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.

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>