0

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.

2
  • 1
    Please consider following these suggestions.
    – mustaccio
    Apr 12 at 0:04
  • If you're expecting a single row that's the sum of the 10 rows you're currently seeing, then you should remove your GROUP BY clause. But as mustaccio pointed out, it's not clear what your goals are with the little details you provided. Please provide more context about your data.
    – J.D.
    Apr 12 at 3:21

1 Answer 1

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.