Saturday, October 20, 2007

ORA-00937: not a single-group group function

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_planwhere membership.membership_plan_id = membership_plan.id;


I got an error message as below:
ERROR at line 1:ORA-00937: not a single-group group function

solution:
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;

No comments: