3 Replies Latest reply: Oct 19, 2007 11:00 AM by 591556 RSS

    ORA-00937: not a single-group group function

    591556
      I want to select some data from table membership and membership_plan, these 2 have some relationships with each other. In the same query, I also want to 'select count(*) from customers' where customers table has no relationship with the other 2 tables, I just want to find out the total number of customers and display with the rest of the query results.
      select (select count(id) from customer), count(membership.id) as total_number_of_members, sum((membership_plan.monthly_price- membership.monthly_discount) * round(months_between(sysdate, membership.join_date),0)) as total_all_members 
      from membership,membership_plan
      where membership.membership_plan_id = membership_plan.id;
      I got an error message as below, can any one please help solve the problem? thanks in advance
      ERROR at line 1:
      ORA-00937: not a single-group group function
        • 1. Re: ORA-00937: not a single-group group function
          bencol
          membership.join_date is not being aggregated, so you'll need to group by that, or remove it from your query. There is not way of telling which row's join_date the query is referring to
          -------------------------------

          Actually, I realised I misread the brackets, so ignore my point

          Message was edited by:
          bencol
          • 2. Re: ORA-00937: not a single-group group function
            86140
            The error is due to (select count(id) from customer).
            Try this:
            select (select count(id) from customer), t.*
              from (select
                           count(membership.id) as total_number_of_members,
                           sum((membership_plan.monthly_price- membership.monthly_discount) *
                                round(months_between(sysdate, membership.join_date),0)) as total_all_members
                      from membership, membership_plan
                     where membership.membership_plan_id = membership_plan.id) t;
            • 3. Re: ORA-00937: not a single-group group function
              591556
              it worked smoradi, thanks very much