termlimit
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER