I expected by query, which uses SUM() to return one row, containing the total of all rows. However, it returns multiple rows. SUM() is not adding the rows.
SELECT SUM(DISTINCT `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`, `sales`.`sale_shipping`;
--- total_shipping --- 70
But I'm getting:
--- total_shipping --- 10 20 40
This seems to be caused by the joins and grouping. How can I get a true SUM()?
The schema and data can be found here: https://www.db-fiddle.com/f/2ex4gAf4SCFzdYEk7GNpaz/0