Postgresql – Duplicate entries in query with sum

postgresql

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!

Benoit

Best Answer

Simple answer, you grouped by your sum. Solution is simply to remove that from your group by statement, eg: http://rextester.com/EOH13219

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,
         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
) t1
GROUP BY t1.player_name_fkey,
         t1.school_name_fkey,
         t1.sum_scores;