you grouped by your sum, PostgreSQL

Language: Layout:

CREATE TABLE schools (
    name text,
    long_name text,
    short_name text CONSTRAINT id PRIMARY KEY,
    address text,
    status text
);

CREATE TABLE players (
    name text,
    school_name text REFERENCES schools (short_name),
    gender text,
    PRIMARY KEY (name, school_name)
);

CREATE TABLE tournament (
    number integer UNIQUE
);

CREATE TABLE double (
    player_name_fkey text,
    school_name_fkey text,
    FOREIGN KEY (player_name_fkey, school_name_fkey) REFERENCES players(name, school_name),
    category text,
    tournament_number int,
    FOREIGN KEY (tournament_number) REFERENCES tournament(number),
    score int
);

INSERT INTO schools (short_name) VALUES ('Harvard'), ('MIT');

INSERT INTO tournament (number) VALUES (1), (2), (3), (4), (5);

INSERT INTO players (name, school_name, gender) VALUES ('Joe', 'Harvard', 'Masculin'), ('Bob', 'MIT', 'Masculin');

INSERT INTO double (player_name_fkey, school_name_fkey, category, tournament_number, score) VALUES ('Joe', 'Harvard', 'Benjamin', 1, 10), ('Joe', 'Harvard', 'Benjamin', 2, 30), ('Joe', 'Harvard', 'Benjamin', 3, 0), ('Joe', 'Harvard', 'Benjamin', 4, 0), ('Bob', 'MIT', 'Benjamin', 1, 40), ('Bob', 'MIT', 'Benjamin', 2, 20), ('Bob', 'MIT', 'Benjamin', 3, 0), ('Bob', 'MIT', 'Benjamin', 4, 0);

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;


absolute service time: 0,39 sec