CODEWITHSUNDEEP
mysqlsql
dabra904
dabra904

Reputation: 163

MySQL Multiple Table Joins incorrect summation

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!

Upvotes: 0

Views: 58

Answers (1)

xQbert
xQbert

Reputation: 35323

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

Upvotes: 1

Related Questions