使用多个 JOINS.SUM() 产生错误的值 [英] Using multiple JOINS. SUM() producing wrong value

问题描述

我在 SQL 查询中获取了一些基本发票信息，并在同一查询中计算了订单总额和付款总额.到目前为止，这是我所拥有的:

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 

除了 totalPayments 列之外，查询的所有内容都非常出色.数据库中有一笔付款，其值为 (10.00).查询提供的值是 20.00(正好是两倍).我的理论是，由于某种原因，该查询将支付金额列求和"两次.谁能帮我解释一下?

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?

感谢您的帮助！

推荐答案

如果您在没有 group by 的情况下运行查询，您会看到某些付款有多行.那是因为您还加入了订单项目.结果集将为 orderitem 和 payment 的每个组合包含一行.

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.

这篇关于使用多个 JOINS.SUM() 产生错误的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！