使用多个连接.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.

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