MySQL：SUM() 與 JOIN 一起使用時不提供總和

這會產生一列有 10 個數字行：

SELECT (`sales`.`sale_shipping`) as `total_shipping`
FROM `sales` 
LEFT JOIN `contacts` ON `sales`.`contact_id` = `contacts`.`contact_id` 
LEFT JOIN `salespayments` ON `salespayments`.`sale_id` = `sales`.`sale_id` 
LEFT JOIN `contactsadditionalreps` ON `contacts`.`contact_id` = `contactsadditionalreps`.`contact_id` 
WHERE `salespayments`.`payment_type`!='Refund' 
GROUP BY `sales`.`sale_id` 

我希望返回 10 個結果的總和，但是不是返回總和，而是返回 10 行具有奇怪值的行：

SELECT SUM(`sales`.`sale_shipping`) as `total_shipping`
FROM `sales` 
LEFT JOIN `contacts` ON `sales`.`contact_id` = `contacts`.`contact_id` 
LEFT JOIN `salespayments` ON `salespayments`.`sale_id` = `sales`.`sale_id` 
LEFT JOIN `contactsadditionalreps` ON `contacts`.`contact_id` = `contactsadditionalreps`.`contact_id` 
WHERE `salespayments`.`payment_type`!='Refund' 
GROUP BY `sales`.`sale_id` 

問題是，它不是對行求和，而是對其他東西求和。

JOINs先完成，然後連同GROUP BY它的聚合，例如SUM.

如果您希望SUM針對單個表完成，則GROUP BY需要針對該表。也許：

... FROM ( SELECT sale_id, sale_shipping
             FROM sales
             GROUP BY sale_id ) AS x
   JOIN ...

引用自：https://dba.stackexchange.com/questions/310807