StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POUsing multiple JOINS. SUM() producing wrong value
text
Body
copied!<p>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:</p> <pre><code>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 </code></pre> <p>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?</p> <p>Thanks for the help!</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload