8 Replies Latest reply: Nov 23, 2012 6:12 AM by 793965 RSS

    count(1) returns null in group by

    793965
      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
          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
            Gaurav Bhide
            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
              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
                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
                  cooool dude. B-)
                  • 6. Re: count(1) returns null in group by
                    793965
                    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
                      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
                        Great Ranit....all confusion gone.. :) :)