CODEWITHSUNDEEP
mysql
Feanor
Feanor

Reputation: 1

SUM and GROUP BY issues

I have a table deliveries and a table orders. Several orders can be linked to a delivery. A few days ago I asked a question about how to get the sum of order amounts for a particular delivery and quickly got shown how. (Thank again for that.)

Now we've added a settlement table as well and just like orders, there can be several settlements linked to one delivery (through the delivery id, just like with orders) and I'm trying to get the sum of their values as well. I figured I'd use the same strategy and LEFT JOIN the settlement table as well, but the problem is that when there's only one settlement linked to a particular delivery the SUM(s.amount) returns double the actual value.

I've tried playing around with GROUP BY to no avail. Can anyone show me what I'm doing wrong?

Thanks in advance!

PS: this is the query I've using that returns the double values for settlement amount when there's only one settlement:

SELECT 
    d.id, 
    SUM(o.goods_amount) AS amount, 
    SUM(s.amount) AS settlementAmount, 
FROM delivery d 
LEFT JOIN order o 
    ON d.id = o.delivery_id 
LEFT JOIN settlement s 
    ON d.id = s.delivery_id 
WHERE d.id = *deliveryId*
GROUP BY d.id;

Upvotes: 0

Views: 67

Answers (2)

DRapp
DRapp

Reputation: 48139

As Spencer commented, you are getting a sort of cross-join result. Lets take a look at this sample data.

Delivery Table
ID  Delivered_On
1   2016-07-01
2   2016-07-02

Orders Table
id  Deliveries_id  Amount
1   1              100
2   1              200
3   1              300
4   2              75

Now your Settlements table
Settlements Table
id  Delivery_id   Amount
1   1             525   (explicitly wrong amount to show result)
2   1             75

Your query is doing a sum per Delivery ID, but (left)joined to both the orders table AND the Settlements table. So, as many records exist in each table will be cross-joined such that for Delivery ID = 1, you have 3 orders but 1 settlement. So the settlement record will be joined to each order table too, not just the delivery table thus giving a result of 3 * 525 or $1575 PLUS 3 * 75 = $225 for a total of $1800. The sum of your orders will properly be $600.

To resolve, you probably need to pre-aggregate each secondary table so it returns only 1 summary record per delivery and join THOSE results. As to prevent querying ALL orders and settlements, I am joining to the delivery table to qualify the same date range in them all.

SELECT 
      d.id AS delivery_id, 
      sumOrd.Amount as OrderAmount,
      sumStl.Amount as SettlementAmount
   from
      delivery d

         LEFT JOIN
         ( select 
                 d2.id,
                 SUM(o.amount) AS OrderAmount
              from
                 delivery d2
                    JOIN order o
                       ON d2.id = o.deliveries_id
              WHERE 
                     d2.delivered_on >= '2016-06-10' 
                 and d2.delivered_on < '2016-06-11'
              GROUP BY 
                 d2.id ) sumOrd
            on d.id = sumOrd.id

         LEFT JOIN
         ( select 
                 d2.id,
                 SUM(s.amount) AS SettlementAmount
              from
                 delivery d2
                    JOIN Settlement s
                       ON d2.id = s.delivery_id
              WHERE 
                     d2.delivered_on >= '2016-06-10' 
                 and d2.delivered_on < '2016-06-11'
              GROUP BY 
                 d2.id ) sumStl
            on d.id = sumStl.id
   where
          d.delivered_on >= '2016-06-10' 
      and d.delivered_on < '2016-06-11'

Upvotes: 1

Mahesh Madushanka
Mahesh Madushanka

Reputation: 2998

first verify your getting correct result using following query 

SELECT 
            d.id, 
            IFNULL(o.goods_amount,0) AS amount, 
            IFNULL(s.amount,0) AS settlementAmount, 
        FROM delivery d 
        LEFT JOIN order o 
            ON d.id = o.delivery_id 
        LEFT JOIN settlement s 
            ON d.id = s.delivery_id 
        WHERE d.id = *deliveryId*

then please check this query 

SELECT 
        d.id, 
        SUM(IFNULL(o.goods_amount,0)) AS amount, 
        SUM(IFNULL(s.amount,0)) AS settlementAmount, 
    FROM delivery d 
    LEFT JOIN order o 
        ON d.id = o.delivery_id 
    LEFT JOIN settlement s 
        ON d.id = s.delivery_id 
    WHERE d.id = *deliveryId*
    GROUP BY d.id;

Upvotes: 0

Related Questions