Fianite Fianite - 1 month ago 6
MySQL Question

Order records based on calculation

I'm currently finding the players with the most goals like such:

Player.joins(:player_stats).group('players.id').order('SUM(player_stats.goals) DESC')


However now I want to order by goals per game, which is the sum of all their goals (player_stats.goals) divided by the number of games they have played (players_stats.length). Is it possible to do arithmetic like this when sorting using order()?

NOTE: I'm working on a mySQL database.

Answer

I think something along these lines would do:

Player.joins(:player_stats)
      .group('players.id')
      .order('(SUM(p‌​layer_stats.goals) / COUNT(player_stats.player_id)) DESC')
Comments