troubleshooting Question

Inner join, problem with single-group group subquery

Avatar of termlimit
termlimitFlag for United States of America asked on
Oracle Database
2 Comments1 Solution289 ViewsLast Modified:
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
Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros