?

Search Help Board

PHP FAQ
PHP Articles
PHP Help
Bulletin Board

PHP Manual (NEW!)
First Time PHP'ers
Help with programming
Sql assignment help
PHP Homework Help


C# Help

?
?Oracle SQL
Author:??(---.gc.maricopa.edu)
Date:???10-12-03 15:37

I am trying to get this sql statement to work but I keep getting this error message. I need to display all order totals (which is the b.retail * a.quantity) that are greater than the avg.order amount which is in the subquery. I don't know what else to try. Any help would be greatly appreciated!

select a.order#,TO_CHAR(sum((b.retail * a.quantity)),'999.99') as order_total,
TO_CHAR(((sum((b.retail * a.quantity)))*.015),'999.99') as surcharge
from orderitems a,
books b,
(select TO_CHAR(avg(sum(b.retail * a.quantity)),'999.99') as avg_order
from orderitems a,
books b
where a.isbn = b.isbn
group by order#) c
where a.isbn = b.isbn
group by a.order#
having sum(b.retail * a.quantity)> c.avg_order
----------------------------------------------------
having sum(b.retail * a.quantity)> c.avg_order
*
ERROR at line 11:
ORA-00979: not a GROUP BY expression

?Topics Author? Date
?Oracle SQL??new
Nicollette 10-12-03 15:37?
Go to Top??|??Go to Topic??|??Flat View??|??Search?
??|??
New Topic
?Reply To This Message
?Your Name:
?Your Email:
?Subject:
Email replies to this thread, to the address above.
??wrote: > > I am trying to get this sql statement to work but I keep > getting this error message. I need to display all order > totals (which is the b.retail * a.quantity) that are greater > than the avg.order amount which is in the subquery. I don't > know what else to try. Any help would be greatly appreciated! > > select a.order#,TO_CHAR(sum((b.retail * > a.quantity)),'999.99') as order_total, > TO_CHAR(((sum((b.retail * a.quantity)))*.015),'999.99') as > surcharge > from orderitems a, > books b, > (select TO_CHAR(avg(sum(b.retail * a.quantity)),'999.99') as > avg_order > from orderitems a, > books b > where a.isbn = b.isbn > group by order#) c > where a.isbn = b.isbn > group by a.order# > having sum(b.retail * a.quantity)> c.avg_order > ---------------------------------------------------- > having sum(b.retail * a.quantity)> c.avg_order > * > ERROR at line 11: > ORA-00979: not a GROUP BY expression ">??

Provided By
Phorum