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

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;