来自具有联接的单个表的SQL求和

您在数据层次结构的两个级别上进行聚合。这会导致每个订单的笛卡尔乘积出现问题。

order_items 之前 做 join :

SELECT SUM(received_amount) as totalRecieved, 
       SUM(order_total) as orderTotal
FROM orders o LEFT JOIN
     (SELECT oi.order_id, SUM(total_items) as total_items
      FROM order_items oi
      GROUP BY oi.order_id
     ) oi
     ON oi.order_id = o.id;

作为替代方案,您可以从cte结构或类似以下的临时表中获益:

;with cte (order_id, SumTotalItems) as (
  SELECT oi.order_id, SUM(total_items) as SumTotalItems
  FROM order_items oi
  GROUP BY oi.order_id
)
select sum(o.receivedamount) as SumReceivedAmount
     , sum(o.order_total) as SumOrderTotal
     , cte.SumTotalItems
from orders o
left outer join cte on o.id = cte.order_id