MySQL Multiple Table Joins incorrect summation

Question

I'm trying to join and sum three tables together (MySQL), grouped by a 'tracking number' and an 'order_ID':

invoice_data_dhl
master_track_nums
payments

Problem: When they are multiple payments rows for an order_ID, it is multiplying the DHL_COST total by the number of results in the payments table for the order_ID.

SELECT 
invoice_data_dhl.Waybill_Number,
sum(invoice_data_dhl.AWB_Original_Amount) AS DHL_COST,
master_track_nums.order_ID,
sum(payments.revenue) AS REVENUE
FROM invoice_data_dhl
LEFT JOIN master_track_nums
ON invoice_data_dhl.Waybill_Number = master_track_nums.track
LEFT JOIN payments
ON master_track_nums.order_ID = payments.order_ID
GROUP BY invoice_data_dhl.Waybill_Number

Can this be written so that I can get the results with a single query that outputs the results like the below?

Waybill_Number  DHL_COST    order_ID    REVENUE
7022193792       182.52       110632      234
4536137201      3311.96       107637     4074
3927382187       248.95       110384      636
7812896712        74.85       106998      247

Thanks for any help in advance!

"Can this be written so that I can get the results with a single query that outputs the results like the below?," Yes. You'll have to aggregate the results before doing the join. Thus an inline query which returns the desired DHL_cost before the added join to payments cause the cost to double. — xQbert
– xQbert, Commented Aug 15, 2014 at 15:17

xQbert · Accepted Answer · 2014-08-15 15:34:22Z

1

UNTESTED: but something like this: Updated: didn't remove sum on revenue in inline query.

SELECT A.waybill_number, A.DHL_COST, A.Order_ID, Sum(payments.Revenue)
FROM 
  (SELECT invoice_data_dhl.Waybill_Number,
     sum(invoice_data_dhl.AWB_Original_Amount) AS DHL_COST,
     master_track_nums.order_ID
   FROM invoice_data_dhl
   LEFT JOIN master_track_nums
       ON invoice_data_dhl.Waybill_Number = master_track_nums.track
   GROUP BY master_track_nums.Order_ID, invoice_data_dhl.Waybill_Number) A
LEFT JOIN payments
  ON A.order_ID = payments.order_ID
GROUP BY A.waybill_number, A.DHL_COST, A.Order_ID

edited Aug 15, 2014 at 15:34

answered Aug 15, 2014 at 15:21

xQbert

35.5k2 gold badges46 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

dabra904 Over a year ago

Thank you! I just had to add in the GROUP BY statement at the end:" GROUP BY A.Waybill_Number" (and match the capitalization in the first line).

Collectives™ on Stack Overflow

MySQL Multiple Table Joins incorrect summation

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related