CODEWITHSUNDEEP
sqlsumjoin
Jesse Bunch
Jesse Bunch

Reputation: 6679

Using multiple JOINS. SUM() producing wrong value

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!

Upvotes: 9

Views: 15720

Answers (3)

Artem Kalinchuk
Artem Kalinchuk

Reputation: 6652

One solution is to do nested queries for each JOIN. It should be a lot quicker than using a correlated query (suggested by Andomar) and will work for database systems that conform with the ANSI SQL standard.

Untested example:

SELECT
  orders_with_payments.billerID,
  orders_with_payments.invoiceDate,
  orders_with_payments.totalPayments,
  SUM(orderItems.itemPrice) AS orderTotal,
  ...
FROM (
  SELECT
    orders.billerID,
    orders.invoiceDate,
    SUM(payments.amount) AS totalPayments,
    ...
  FROM orders
  LEFT JOIN payments ON payments.orderID = orders.id
) orders_with_payments
LEFT JOIN orderItems ON orderItems.orderID = orders_with_payments.id

Upvotes: 0

Nicholai
Nicholai

Reputation: 818

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.

Upvotes: 0

Andomar
Andomar

Reputation: 238176

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.

Upvotes: 25

Related Questions