CODEWITHSUNDEEP
mysqlsqlrdbms
Ofek Ron
Ofek Ron

Reputation: 8580

How to find MAX over SUMs MySQL

Consider the following scenario : 

an Item has a Price

a Order contains Items

a Delivery contains Orders

I want to query for each delivery, the order with the highest price, where a price of an order is the summation of prices of the contained items.

A simple sufficient schema would look like this :

Delivery

d_id

Order

o_id

Item

i_id,price

ItemsInOrder

o_id,i_id

OrdersInDelivery

d_id,o_id

I am stuck in the point of having the summation results, needing to get the max order per delivery :

SELECT d_id,o_id,SUM(price)
from ItemsInOrder
natural join OrdersInDelivery
natural join Item
group by d_id,o_id

How should i go from here to get that each d_id, would appear once and aside the o_id with the maximal price summation?

Upvotes: 2

Views: 119

Answers (4)

Ofek Ron
Ofek Ron

Reputation: 8580

Thanks for all your answers, but none of them was what i was looking for. I finally found what i was looking for and the approach i choose is as follows :

SELECT d_id,o_id,sum_price
FROM (
    SELECT d_id,o_id,SUM(price) as sum_price
    from ItemsInOrder
    natural join OrdersInDelivery
    natural join Item
    group by d_id,o_id
    order by d_id,sum_price desc
) as sums
GROUP BY d_id

Upvotes: 1

One Man Crew
One Man Crew

Reputation: 9578

You can use Left Joining with self, tweaking join conditions and filters. In this approach, you left join the table with itself. Equality, of course, goes in the group-identifier. 

SELECT a.*
FROM (
        SELECT oid.d_id AS d_id, o_id, SUM(price) AS sum_price
        FROM `OrdersInDelivery` oid
        INNER JOIN `ItemsInOrder` iio USING (o_id) 
        INNER JOIN `Item` i USING (i_id)
        GROUP BY d_id, o_id
      ) a
LEFT OUTER JOIN (
                  SELECT oid.d_id AS d_id, o_id, SUM(price) AS sum_price
                  FROM `OrdersInDelivery` oid
                  INNER JOIN `ItemsInOrder` iio USING (o_id) 
                  INNER JOIN `Item` i USING (i_id)
                  GROUP BY d_id, o_id
                )b
    ON a.d_id = b.d_id  AND a.sum_price < b.sum_price
WHERE b.d_id IS NULL  AND b.o_id IS NULL;

Take a look at this query in SQL fiddle: http://sqlfiddle.com/#!9/d5c04d/1

In that case it's recommended to use TEMPORARY TABLE:

    CREATE TEMPORARY TABLE OrderSummary(
     SELECT oid.d_id AS d_id, o_id, SUM(price) AS sum_price
            FROM `OrdersInDelivery` oid
            INNER JOIN `ItemsInOrder` iio USING (o_id) 
            INNER JOIN `Item` i USING (i_id)
            GROUP BY d_id, o_id
    );
CREATE TEMPORARY TABLE OrderSummary2(
     SELECT * FROM OrderSummary
    );

    SELECT a.*
    FROM OrderSummary a
    LEFT OUTER JOIN OrderSummary2 b
        ON a.d_id = b.d_id  AND a.sum_price < b.sum_price
    WHERE b.d_id IS NULL  AND b.o_id IS NULL;

Upvotes: 1

Elie M
Elie M

Reputation: 263

I'm sharing the results that I've got so far so anyone could continue to help you because i need to leave to airport now :) I'll try to check it again tomorrow if no one figure it out.

its a challenging question guys.

I have already prepared some samples here : http://sqlfiddle.com/#!9/d4fed2

And the latest query i did is : 

SELECT d_id,OID.o_id,sumPrice
from OrdersInDelivery OID
join (SELECT IIO.o_id,SUM(I.price) as sumPrice
from ItemsInOrder IIO 
join item I on I.i_id = IIO.i_id
group by IIO.o_id) IIO1 on OID.o_id = IIO1.o_id

Good luck :)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269933

Don't use natural join. They are an error waiting to happen, because they depend on the names of columns in the tables -- not even using properly and explicitly defined foreign key relationships. Use on or using instead.

This type of query is painful in MySQL, because MySQL does not support CTEs (common table expressions) nor does it support window functions. So, the query is more complicated than in other databases:

select oid.d_id, o_id, SUM(price)
from OrdersInDelivery oid join
     ItemsInOrder iio
     using (o_id)
     Item i
     using (i_id)
group by d_id, o_id
having sum(price) = (select sum(iio2.price)
                     from ItemsInOrder iio2 join
                          Item i2
                          using (i_id)
                     where iio2.d_id = iio.d_id
                     group by iio2.o_id
                     order by sum(iio2.price) desc
                     limit 1
                    );

Upvotes: 0

Related Questions