| sql ? [message #2634] |
Thu, 01 August 2002 12:36  |
Oracle Begineer
Messages: 2
Registered: April 2002
|
Junior Member |
|
|
If you have a
Table ORDER with column Order_id,
Table ORDERPRODUCT with Order_id, Product_id, Quantity,
Table PRODUCT with Product_id, Unit_price
What is the biggest Order of the day?
This portion of my SQL works but how do i get the Order_id?
SELECT MAX(SUM(UNIT_PRICE*QUANTITY)) BIGGEST_ORDER
FROM ORDERPRODUCT OP, PRODUCT P
WHERE OP.PRODUCT_ID = P.PRODUCT_ID
GROUP BY OP.ORDER_ID;
|
|
|
|
| Re: sql ? [message #2635 is a reply to message #2634] |
Thu, 01 August 2002 15:58  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Hopefully this might get you started:
select o.order_id, sum(op.quantity * p.unit_price)
from orders o, orderproduct op, product p
where o.order_date >= trunc(sysdate) and o.order_date < trunc(sysdate) + 1
and op.order_id = o.order_id
and p.product_id = op.product_id
group by o.order_id
having sum(op.quantity * p.unit_price) =
(select max(sum(op.quantity * p.unit_price))
from orders o, orderproduct op, product p
where o.order_date >= trunc(sysdate) and o.order_date < trunc(sysdate) + 1
and op.order_id = o.order_id
and p.product_id = op.product_id
group by op.order_id); |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
