How to select Order with highest sum of line items price

byMR

November 28, 2023

I have two tables in mysql :
Orders (id, orderName)
Order_item (id, partname, price, order_id)

Order_id in order item is foreign key to Order table.

How to select order with highest sum of order_item price?

can you please advice me how to do it in oracle and mysql?

I try to do it with this query:

SELECT ord.* FROM ORDERS ord WHERE ID =  (Select  o.id from  ORDERS o
inner join order_item oi on o.id =oi.order_id
group by oi.id,ord.orderName
order by sum(oi.price) desc limit 1)

but it select order with highest price of order_item.price.

;

Note that select top 1 is not valid statement in mysql.

Is this correct solution ?:

SELECT ord.* FROM ORDERS ord WHERE ID =  (  Select  o.id from  ORDERS o
inner join order_item oi on o.id =oi.order_id group by o.id order by sum(oi.price) desc limit 1);

At least on Mysql server it runs. How to rewrite this to correct Oracle sql statement?

>Solution :

In Oracle:

SELECT o.id,
       MAX(o.ordername) AS ordername
FROM   "ORDER" o
       INNER JOIN Order_item oi
       ON (o.id = oi.order_id)
GROUP BY o.id
ORDER BY SUM(oi.price) DESC
FETCH FIRST ROW ONLY;

Note: ORDER is a reserved word and cannot be used as an unquoted identifier. Either use a quoted identifier (and always use the correct case) or change to a different identifier such as orders.

In MySQL:

SELECT o.id,
       MAX(o.ordername) AS ordername
FROM   Order o
       INNER JOIN Order_item oi
       ON (o.id = oi.order_id)
GROUP BY o.id
ORDER BY SUM(oi.price) DESC
LIMIT 1;