Search code examples
mysqlsqlselectpercentageheidisql

Calculate win percentage in SQL from two other columns

http://i58.tinypic.com/nqvlep.png

Hi,

So I'm trying to create a win percentage column for each horse that I have in this horse racing table above. A win is classified as 1 with any finish where place = 1,2,3 and 0 otherwise, and I have already created the win column.

  • I would like to count the number of races each horse has been in and divide the sum(wins) for each horse by its count(race) for each horse to get the win percentage.

P.S. Each horse is in multiple races and with with a different place finish in the race if that wasn't clear.

Thank you!

Solution

  • SQL has sum and count aggregate functions. You just need to group by the horse:

    SELECT   horse, SUM(win) / COUNT(*)  * 100 AS win_percent
FROM     some_table
GROUP BY horse