Select distinct columns from table tournament and max winner player

I’ve search a bit, without an answer, I’m doing an exercise and I have 4 tables

Table “tournament as t”

ID NAME CODE
1 Roland Garros RG
2 Australian open AO
3 US open US
4 Wimbledon WI

Table “trial as e”

ID YEAR GENDER_TRIAL ID_TOURNAMENT (fk_toTournament)
1 2010 Male 1
2 2010 Female 1
3 2011 Female 2
4 2011 Male 2
5 2011 Male 1

Table “match_tennis as mt”

ID ID_trial (fk_toTrial) ID_winner (fk_toPlayer) ID_loser (fk_toPlayer)
1 2 1 2
2 3 3 1
3 1 2 3

Table “player as p” | ID | name | gender | |:–:|:—————:|:——:| | 1 | Rafael Nadal | Male | | 2 | Roger Federer | Male | | 3 | Serena Williams | Female | | 2 | Williams Venus | Female |

So my tables have many records, and the expected result is

ID (t.id) Tournament (t.Name) Code (t.Code) Female Player with most wins (p.Name) Wins (MAX(COUNT(WINNER))) Male Player with most wins (p.Name) Wins (MAX(COUNT(WINNER)))
1 Roland Garros RG Serena Williams 5 Rafael Nadal 7
2 Australian open AO Venus Williams 3 Roger Federer 9
3 US open US Simona Halep 7 Novak Djokovic 5
4 Wimbledon WI Wozniacki Caroline 3 Andy Murray 4

Something like that … I can’t change my database tables, so far I got all the tournaments with all the wins from each player

my query is like that

SELECT t.id, t.name, t.code, p.name, COUNT(*) as wins "
                + "FROM tournament t "
                + "INNER JOIN trial e ON e.id_tournament = t.id "
                + "INNER JOIN match_tennis mt ON mt.id_trial = e.id "
                + "INNER JOIN player p ON mt.id_winner = p.id "
                + "GROUP BY t.name, p.name "
                + "ORDER BY wins DESC";

And I have something like this as the expected output https://prnt.sc/112yvgr

Answer

You need to start by thinking of how to get each player/tournament combination with the number of wins.

SELECT t.id, t.name, t.code, e.gender_trial, p.name as player_name, COUNT(*) as wins
FROM tournament t INNER JOIN
     trial e
     ON e.id_tournament = t.id INNER JOIN
     match_tennis mt
     ON mt.id_trial = e.id INNER JOIN
     player p
     ON mt.id_winner = p.id
GROUP BY t.id, t.name, t.code, e.gender_trial, p.name;

This is very similar to your query, but the GROUP BY and SELECT are consistent and it also includes the gender.

With this result you can do two things. The first is to calculate the first per gender. The second is to aggregate each tournament into one row. So:

WITH tp as (
      SELECT t.id, t.name, t.code, e.gender_trial, p.name, COUNT(*) as wins 
      FROM tournament t INNER JOIN
           trial e
           ON e.id_tournament = t.id INNER JOIN
           match_tennis mt
           ON mt.id_trial = e.id INNER JOIN
           player p
           ON mt.id_winner = p.id
      GROUP BY t.id, t.name, t.code, e.gender_trial, p.name
     )
SELECT t.id, t.name, t.code,
       MAX(CASE WHEN seqnum = 1 AND gender_trial = 'Female' THEN player_name END)) as female_first, 
       MAX(CASE WHEN seqnum = 1 AND gender_trial = 'Female' THEN wins END)) as female_wins,
       MAX(CASE WHEN seqnum = 1 AND gender_trial = 'Male' THEN player_name END)) as male_first, 
       MAX(CASE WHEN seqnum = 1 AND gender_trial = 'Male' THEN wins END)) as male_wins
FROM (SELECT tp.*,
             ROW_NUMBER() OVER (PARTITION BY id, gender_trial ORDER BY wins DESC) as seqnum
      FROM tp
     ) tp
GROUP BY t.id, t.name, t.code;