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
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
Yes, this can help writing pivot queries etc…
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)
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” ?
Count() is just like COUNT(*) … (as long as the constant is not NULL :)
thanks i was about to use count(*) but then thought it is good to do some research before using it… and i got here..
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?
@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”
thank you