Jayson Tofu Jayson Tofu - 27 days ago 6
MySQL Question

SUM SQL multiple tables GROUP BY

Hello I can't seem to get this question answered.

enter image description here

the question is :
3.5 For each horse that has won a prize, list the horse name, horse_id and total prize money won.

have tried:

SELECT horse_name, horse.horse_id, SUM(prizemoney)
FROM horse
JOIN entry ON entry.horse_id=horse.horse_id
JOIN prize ON entry.event_id=prize.event_code
GROUP BY horse_name


dont get the right answer

This is my answer:

enter image description here

This is the teachers answer:

enter image description here

Answer Source

If you ignore PLACE in your joins then you will get the wrong answer. Include PLACE in the join logic!

SELECT horse_name, horse.horse_id, SUM(prizemoney) 
FROM horse 
JOIN entry ON entry.horse_id=horse.horse_id 
JOIN prize ON entry.event_id=prize.event_code 
          AND entry.place = prize.place
GROUP BY horse_name, horse.horse_id

also: include ALL non-aggregating columns in the grouop by clause