SQL Exercise: Club that supplied the most players to the 2016 EURO

Last update on January 16 2026 12:32:42 (UTC/GMT +8 hours)

13. From the following table, write a SQL query to find the club, which supplied the most number of players to the 2016-EURO cup. Return club name, number of players.

Sample table: player_mast

 player_id | team_id | jersey_no |       player_name       | posi_to_play | dt_of_bir  | age |    playing_club
-----------+---------+-----------+-------------------------+--------------+------------+-----+---------------------
    160001 |    1201 |         1 | Etrit Berisha           | GK           | 1989-03-10 |  27 | Lazio
    160008 |    1201 |         2 | Andi Lila               | DF           | 1986-02-12 |  30 | Giannina
    160016 |    1201 |         3 | Ermir Lenjani           | MF           | 1989-08-05 |  26 | Nantes
    160007 |    1201 |         4 | Elseid Hysaj            | DF           | 1994-02-20 |  22 | Napoli
    160013 |    1201 |         5 | Lorik Cana              | MF           | 1983-07-27 |  32 | Nantes
    160010 |    1201 |         6 | Frederic Veseli         | DF           | 1992-11-20 |  23 | Lugano
    160004 |    1201 |         7 | Ansi Agolli             | DF           | 1982-10-11 |  33 | Qarabag
    160012 |    1201 |         8 | Migjen Basha            | MF           | 1987-01-05 |  29 | Como
    160017 |    1201 |         9 | Ledian Memushaj         | MF           | 1986-12-17 |  29 | Pescara
........
    160548 |    1224 |        23 | Simon Church            | FD           | 1988-12-10 |  27 | MK Dons

View the table

Sample Solution:

SQL Code:

-- This SQL query retrieves the playing clubs and the count of players for each playing club
-- where the count of players is equal to the maximum count of players among all playing clubs.

SELECT playing_club, COUNT(playing_club) 
-- Selects the 'playing_club' and the count of players for each playing club.
FROM player_mast  
-- 'player_mast' is the name of the table being queried.
GROUP BY playing_club 
-- Groups the results by 'playing_club'.
HAVING COUNT (playing_club) = (
-- The HAVING clause filters groups where the count of players is equal to the maximum count in the subquery.
    SELECT MAX(mycount) 
    -- This subquery selects the maximum count of players.
    FROM ( 
        SELECT playing_club, COUNT(playing_club) mycount 
        -- This sub-subquery calculates the count of players for each playing club and aliases it as 'mycount'.
        FROM player_mast 
        -- 'player_mast' is the name of the table involved in the sub-subquery.
        GROUP BY playing_club
        -- Groups the results by 'playing_club'.
    ) pm
);

Sample Output:

 playing_club | count
--------------+-------
 Juventus     |    12
 Liverpool    |    12
(2 rows)

Code Explanation:

The said query in SQL that selects the playing club and the count of players belonging to each playing club from the player_mast table.
Filters the results to only show the clubs with the maximum number of players. It does this by first running a subquery that selects the maximum count of players from the player_mast table. This subquery is then used in the outer query as a filter condition for the HAVING clause. Only the playing clubs that have the same count as the maximum count will be returned.

Alternative Solution:

Using Window Functions:


SELECT playing_club, mycount
FROM (
    SELECT playing_club, COUNT(playing_club) mycount,
           RANK() OVER (ORDER BY COUNT(playing_club) DESC) rnk
    FROM player_mast
    GROUP BY playing_club
) ranked
WHERE rnk = 1;

Explanation:

This query uses a subquery to calculate the counts and ranks of playing_club. It then selects the rows where the rank is 1.

Go to:

PREV : The team Portugal defeated in the EURO cup 2016 final.
NEXT : Player and jersey number who scored the first penalty.

Practice Online