This content has been marked as final. Show 8 replies
When you just use count on it's own (and you should use count(*) not count(1) as the optimiser will re-write it internally to count(*) anyway)... then you are saying that you want a count of all the rows.
When you introduce the group by clause you are saying thay you want a count of records within particular groups... but if there are no groups returned, it can't give you a row for any group with it's equivalent count, because there is not rows for the groups. With grouping, you've have to have at least 1 row per group to get a count for it.
As data is not present in table which will satisfy your where condition, oracle can not group by on values which are not exists.
See this simple example. In below example, 3 is not present in table, hence nothing to group with.
SQL> with test_table as 2 ( 3 select 1 as id from dual 4 union all 5 select 2 as id from dual 6 )select * from test_table; ID ---------- 1 2 SQL> with test_table as 2 ( 3 select 1 as id from dual 4 union all 5 select 2 as id from dual 6 )select count(*) from test_table where id = 3; COUNT(*) ---------- 0 SQL> with test_table as 2 ( 3 select 1 as id from dual 4 union all 5 select 2 as id from dual 6 )select count(*) from test_table where id = 3 group by id; no rows selected
Here, it is simple -
SELECT COUNT(1) FROM book_table WHERE client_id = 10009 AND book_id = 5465465 AND book_sub_id = 'gfdf' AND amount = 78686
You filter everything and then try to get the count. Since no records match, you get count = 0.
Here, case is slightly different.
SELECT COUNT(1) FROM book_table WHERE client_id = 10009 AND book_id = 5465465 AND book_sub_id = 'gfdf' AND amount = 78686 group by client_id,book_id,book_sub_id,amount
The output is Count of the no of groups... which is 0.
gogol wrote:Don't think like that sandy.
Again I am thinking...the COUNT() is an aggregate function. Now a function should return something(as per my plsql knowledge) and in this case the return datatype is integer. So why isnt it returning zero..
The Group By is actually done on an empty result set, so the result is neither 0 nor NULL
It is an empty result set.
Check this -- http://stackoverflow.com/questions/2552086/does-count-always-return-a-result
The "return value of the 'count' function" is ALWAYS a non-null integer, without exception. By mentioning "group by", you're referencing the containing query and changing the subject of "return value" from "count function" to "query's result set". A non-grouped count query produces a result set of a single record containing the return value of count. Alternatively, a grouped count query produces a result set where each record contains a count value. In that case, if there are no groups for count to run on, count is never run and the "query return value" is an empty set.
Hope this Helps.
Edited by: ranit B on Nov 23, 2012 5:25 PM