Using multiple JOINS. SUM() producing wrong value.

Question

Hey folks,

I am getting some basic invoice information in a SQL query and figuring the Order Total and Payment Totals in the same query. Here is what I have thus far:

SELECT
    orders.billerID, 
    orders.invoiceDate, 
    orders.txnID, 
    orders.bName, 
    orders.bStreet1, 
    orders.bStreet2, 
    orders.bCity, 
    orders.bState, 
    orders.bZip, 
    orders.bCountry, 
    orders.sName, 
    orders.sStreet1, 
    orders.sStreet2, 
    orders.sCity, 
    orders.sState, 
    orders.sZip, 
    orders.sCountry, 
    orders.paymentType, 
    orders.invoiceNotes, 
    orders.pFee, 
    orders.shipping, 
    orders.tax, 
    orders.reasonCode, 
    orders.txnType, 
    orders.customerID, 
    customers.firstName AS firstName, 
    customers.lastName AS lastName, 
    customers.businessName AS businessName, 
    orderStatus.statusName AS orderStatus, 
    SUM((orderItems.itemPrice * orderItems.itemQuantity))
      + orders.shipping + orders.tax AS orderTotal, 
    SUM(payments.amount) AS totalPayments                       <-- this sum
FROM
    orders 
    LEFT JOIN customers ON orders.customerID = customers.id 
    LEFT JOIN orderStatus ON orders.orderStatus = orderStatus.id
    LEFT JOIN payments ON payments.orderID = orders.id          <-- this join
    LEFT JOIN orderItems ON orderItems.orderID = orders.id 

Everything comes out of the query wonderfully except the totalPayments column. There is one payment in the database with the value of (10.00). The value provided by the query is 20.00 (exactly double). My theory is that, for some reason, the query is "summing" the payment amount column twice. Can anyone shed some light on this for me?

Thanks for the help!

Answer 1

If you run the query without a group by, you'll see that some payments have multiple rows. That's because you're also joining on order items. The result set will contain a row for each combination of orderitem and payment.

One solution would be to change the sum to:

,    <earlier columns>    
,    (   select SUM(payments.amount) 
         from payments 
         where payments.orderID = orders.id
     ) AS totalPayments
,    <later columns>

This would ensure the payments with multiple orderitems are not summed multiple times.

Answer 2

my guess is that the payment is $10 and there are two items in the order (orderitems table). if that's the case, try using a GROUP BY on the orders/customers/orderstatus fields.