Lecture
1. MySQL joins combined with SUM and COUNT can return an incorrect result
example
SELECT t1.name, COUNT(t2.id), SUM(t2.total) FROM users t1 LEFT JOIN orders t2 ON t1.id = t2.user_id
GROUP BY t1.id
In this case, what gets summed is not the sum of orders for each user, but
the sum of each user's orders multiplied by the number of users,
this can be fixed by using a subquery
SELECT t1.name, cnt, amount FROM users t1 LEFT JOIN ( select user_id COUNT(t2.id) as cnt,
SUM(t2.total) as amount from orders group by user_id ) t2 ON t1.id = t2.user_id
GROUP BY t1.id
Comments