Link to home
Start Free TrialLog in
Avatar of termlimit
termlimitFlag for United States of America

asked on

Inner join, problem with single-group group subquery

I am attempting to create a listing of customer orders related to a order_items table.  I want to count the total number of items ordered by the customer and associate that with the customer.  I can pick out the seven rows that contain the following:

select sum(quantity) "Number Purchased" from order_items
join bookorder on bookorder.order# = order_items.order#
join bookcustomer on bookcustomer.customer# = bookorder.customer#
group by bookorder.customer# having sum(quantity) > 2;

That gives the following:
Number Purchased
----------------
               5
               5
               8
               3
               3
               3
               4

That is correct.  No i need to join that to a table the has the customer information, along with another table that holds the order information.  Here is what I have tried so far:

select bookcustomer.lastname, bookcustomer.state, sum(quantity) from bookcustomer
join bookorder on bookorder.customer# = bookcustomer.customer#
join order_items on bookorder.order# = order_items.order#
where (
select sum(quantity) from order_items
join bookorder on bookorder.order# = order_items.order#
group by bookorder.customer#) > 2;

This gives me ORA-00937: not a single-group group function error on the bookcustomer.lastname not being a single group function.

I have tried everything I could think of and that is why I am here, again!

I would like to get the following:

lastname        state      city         Number Ordered
Name1           State1    City1       5
Name2           State2    City2       5
Name3           State3    City3       8
Name4           State4    City4       3
Name5           State5    City5       3
Name6           State6    City6       3
Name7           State7    City7       4

That would be my ideal output at the moment.
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of termlimit

ASKER

OK, I see how you did it now.  Yea create the result set entirely in the ( ) then compare that to the bookcustomer table.  OK, thanks for the help again!