I have the following tables:
players: Columns: name, school_name, gender
double: Columns: player_name_fkey, school_name_fkey, category, tournament_number, score
My entries in the simple table will be similar to this. Each line is the score by a player at a specific tournament number.
Joe, Harvard, Junior, 1, 40 Joe, Harvard, Junior, 2, 30 Joe, Harvard, Junior, 3, 60 Anne, MIT, Senior, 1, 45 Anne, MIT, Senior, 2, 55 Anne, MIT, Senior, 3, 20 etc.
What I am looking to do is to get is, for each player, its total score over all tournaments. I then intent to select the best five players per school (which is why the partition command).
I have the following query:
SELECT player_name_fkey, school_name_fkey, sum_scores, rank() OVER (PARTITION BY school_name_fkey ORDER BY sum_scores DESC) AS scorerank FROM ( SELECT player_name_fkey, school_name_fkey, score, sum(score) AS sum_scores FROM double LEFT JOIN players ON players.name = double.player_name_fkey WHERE category = 'Benjamin' AND gender = 'Masculin' AND tournament_number >= 2 GROUP BY player_name_fkey, school_name_fkey, score ) t1 GROUP BY t1.player_name_fkey, t1.school_name_fkey, t1.sum_scores;
I do get the correct sum for players, but unfortunately, I get an extra duplicate row for each player with a value of zero and I have no idea why.
Here is the code: http://rextester.com/XRRX92434
Thanks in advance for any help!