Prize Money - Historically

Question

Hi,  how do I get the amount of prize money won by each horse historically?

thanks

Answer 1

Like this:

mysql>

select name, sum(prize_money) from historic_races join historic_runners using (race_id) group by name;

Add a bit more to the SQL and you get to see some of the best horses ever at the top of the query :)

mysql>

select name, sum(prize_money) from historic_races join historic_runners using (race_id) group by name order by sum(prize_money) DESC;

And if you can't see them because there are 150k + horses in the query, then

mysql>

select name, sum(prize_money) from historic_races join historic_runners using (race_id) group by name order by sum(prize_money) DESC limit 5;

As you'd expect, Frankel (surely everyone's favourite horse ever?), with almost £3M in prize money won, is top.