Duplicate entries in query with sum

Question

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

Answer 1

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;

Answer 2

You don't need the derived table, you can use the window function to order by the sum.

The LEFT JOIN works as an INNER JOIN as columns from both tables appear in the WHERE clause, so it's probably better to explicitly state it as an inner join.

I added the school_name in the joining condition. It seems like it be there but it's not very clear in the question description. (Could a player be in one school and appear in a different school in the double table? The foreign key definition indicates that it can't.)

You state that you want "for each player, its total score over all tournaments" but the code has WHERE tournament_number >= 2. Which is correct?

SELECT d.player_name_fkey,
       d.school_name_fkey,
       sum(d.score) AS sum_scores,
       rank() OVER w AS scorerank
FROM players AS p 
    JOIN double AS d
    ON  p.name = d.player_name_fkey
    AND p.school_name = d.school_name_fkey
WHERE p.gender = 'Masculin' 
  AND d.category = 'Benjamin'
  AND d.tournament_number >= 2
GROUP BY d.player_name_fkey,
         d.school_name_fkey 
WINDOW w AS (PARTITION BY d.school_name_fkey ORDER BY sum(d.score) DESC) ;