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`;
I'm expecting:
--- 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
sales
.sale_shipping
) astotal_shipping