Using multiple JOINS. SUM() producing wrong value

Question

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!

Hi, and welcome to StackOverflow. To format code, like SQL, select it and hit Ctrl+K, this will indent the block by 4 spaces, which will be interpreted by the scripts on this site to mean code, and will thus be reformatted accordingly. — Lasse V. Karlsen
– Lasse V. Karlsen, Commented Feb 20, 2010 at 18:18

Andomar · Accepted Answer · 2010-02-20 18:16:26Z

25

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.

answered Feb 20, 2010 at 18:16

Andomar

239k55 gold badges387 silver badges412 bronze badges

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

4 Comments

Jesse Bunch Over a year ago

Makes great sense! Thanks for the quick reply +1!

Fabian Bigler Over a year ago

I think this just saved my day

Joyner Over a year ago

Its a good solution when you want to use join(which returns multiple rows) and sum

Artem Kalinchuk Over a year ago

This is a correlated subquery which is a lot slower.

Nicholai · Accepted Answer · 2010-02-20 18:20:51Z

0

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.

answered Feb 20, 2010 at 18:20

Nicholai

8187 silver badges17 bronze badges

Comments

Artem Kalinchuk · Accepted Answer · 2021-08-11 15:29:20Z

0

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

answered Aug 11, 2021 at 15:29

Artem Kalinchuk

6,6929 gold badges45 silver badges59 bronze badges

Collectives™ on Stack Overflow

Using multiple JOINS. SUM() producing wrong value

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related