MySQL: SUM() returns multiple rows when used with joins and grouping

Question

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

Answer 1

When you use the GROUP BY clause, your result set will contain one row per unique value combination of the fields you're grouping on. Your query groups on the sales.sales_id field and in your example dataset you have multiple different sales_ids. That is why you're getting multiple rows back, and the SUM() of the data in each row is only across the source rows of that row's sales_id.

Since you're not SELECTing those fields, and it sounds like you want a single SUM() across all rows, you don't need to use a GROUP BY clause here. The following query without it should be all you need:

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';

Also, side note, because you're explicitly filtering on WHERE salespayments.payment_type !='Refund'; then you don't need to use a LEFT (outer) JOIN on salespayments. Every row has to match on the join in order for salespayments.payment_type to not be null. Therefore you can use an INNER JOIN instead, which may be more efficient in some cases.

Answer 2

Try to use same query wihout GROUP BY statement.

Need to Remove

GROUP BY `sales`.`sale_id`, `sales`.`sale_shipping`; 

I worked on you given schema and data & found some results.

Results with GROUP BY statement.

Results without GROUP BY statement.

Please read - The GROUP BY clause groups a set of rows into a set of summary rows by values of columns or expressions. The GROUP BY clause returns one row for each group. In other words, it reduces the number of rows in the result set.