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



  • 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



  • 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.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2