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



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



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



Suggested Topics

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