This discussion is archived
8 Replies Latest reply: Nov 23, 2012 4:12 AM by 793965 RSS

count(1) returns null in group by

793965 Explorer
Currently Being Moderated
hi gems..good afternoon..

I read that the COUNT() function always returns 0 (zero) if there is no matching rows in the table.

The following code returns the 0 as expected:
SELECT COUNT(1) FROM book_table
             WHERE client_id = 10009
               AND book_id = 5465465
               AND book_sub_id = 'gfdf'
               AND amount = 78686
But when I used the GROUP BY clause with the query, then it returned nothing:
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
Why this is happening..please suggest...
  • 1. Re: count(1) returns null in group by
    BluShadow Guru Moderator
    Currently Being Moderated
    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.
  • 2. Re: count(1) returns null in group by
    GauravBhide Newbie
    Currently Being Moderated
    Hi,

    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
  • 3. Re: count(1) returns null in group by
    ranit B Expert
    Currently Being Moderated
    Hi Sandy,
    SELECT COUNT(1) FROM book_table
    WHERE client_id = 10009
    AND book_id = 5465465
    AND book_sub_id = 'gfdf'
    AND amount = 78686
    Here, it is simple -
    You filter everything and then try to get the count. Since no records match, you get count = 0.

    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
    Here, case is slightly different.
    The output is Count of the no of groups... which is 0.
  • 4. Re: count(1) returns null in group by
    793965 Explorer
    Currently Being Moderated
    Thanks all for your kind replies...

    Actually I got confused regarding "count will give 0 if nothing is found"...this is wrong...

    Now I got it...
  • 5. Re: count(1) returns null in group by
    ranit B Expert
    Currently Being Moderated
    cooool dude. B-)
  • 6. Re: count(1) returns null in group by
    793965 Explorer
    Currently Being Moderated
    But Ranit...

    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..
  • 7. Re: count(1) returns null in group by
    ranit B Expert
    Currently Being Moderated
    gogol wrote:
    But Ranit...

    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..
    Don't think like that sandy.
    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.
    Ranit B.

    Edited by: ranit B on Nov 23, 2012 5:25 PM
  • 8. Re: count(1) returns null in group by
    793965 Explorer
    Currently Being Moderated
    Great Ranit....all confusion gone.. :) :)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points