MySQL: SUM() does not provide sum when used with JOIN

Question

This produces one column with 10 numerical rows:

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` 

I would like the sum to be returned of the 10 results, however instead of returning the sum, this returns 10 rows with strange values:

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` 

The problem is, instead of summing the rows, it sums something else.

• mustaccio: Please consider following these suggestions.
• J.D.: If you're expecting a single row that's the sum of the 10 rows you're currently seeing, then you should remove your GROUP BY clause. But as mustaccio pointed out, it's not clear what your goals are with the little details you provided. Please provide more context about your data.

Answer 1

The JOINs are done first, then the GROUP BY together with its aggregates, such as SUM.

If you want the SUM to be done against a single table, then the GROUP BY needs to be against that one table. Maybe:

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