SQL Exercise: How many games the goalkeeper played for his team?
24. From the following tables, write a SQL query to find the number of matches played by a player as a goalkeeper for his team. Return country name, player name, number of matches played as a goalkeeper.
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
Sample table: match_details
match_no | play_stage | team_id | win_lose | decided_by | goal_score | penalty_score | ass_ref | player_gk
----------+------------+---------+----------+------------+------------+---------------+---------+-----------
1 | G | 1207 | W | N | 2 | | 80016 | 160140
1 | G | 1216 | L | N | 1 | | 80020 | 160348
2 | G | 1201 | L | N | 0 | | 80003 | 160001
2 | G | 1221 | W | N | 1 | | 80023 | 160463
3 | G | 1224 | W | N | 2 | | 80031 | 160532
3 | G | 1218 | L | N | 1 | | 80025 | 160392
4 | G | 1206 | D | N | 1 | | 80008 | 160117
4 | G | 1217 | D | N | 1 | | 80019 | 160369
5 | G | 1222 | L | N | 0 | | 80011 | 160486
........
51 | F | 1207 | L | N | 0 | | 80007 | 160140
Sample table: soccer_country
country_id | country_abbr | country_name
------------+--------------+---------------------
1201 | ALB | Albania
1202 | AUT | Austria
1203 | BEL | Belgium
1204 | CRO | Croatia
1205 | CZE | Czech Republic
1206 | ENG | England
1207 | FRA | France
1208 | GER | Germany
.......
1229 | NOR | Norway
Sample Solution:
SQL Code:
-- This query selects the country name, player name, and count of goalkeepers for each country.
SELECT
b.country_name, -- Selecting the country name from the soccer_country table aliased as 'b'
c.player_name, -- Selecting the player name from the player_mast table aliased as 'c'
COUNT(a.player_gk) count_gk -- Counting the occurrences of goalkeeper player IDs from the match_details table aliased as 'a'
FROM
match_details a -- Specifying the match_details table with an alias 'a'
JOIN
soccer_country b ON a.team_id = b.country_id -- Joining the soccer_country table with the match_details table based on the team_id
JOIN
player_mast c ON a.player_gk = c.player_id -- Joining the player_mast table with the match_details table based on the goalkeeper player ID
GROUP BY
b.country_name, c.player_name -- Grouping the results by country name and player name
-- Ordering the results by country name and player name, with goalkeeper count in descending order
ORDER BY
country_name, player_name, count_gk DESC;
Sample Output:
country_name | player_name | count_gk
---------------------+---------------------+----------
Albania | Etrit Berisha | 3
Austria | Robert Almer | 3
Belgium | Thibaut Courtois | 5
Croatia | Danijel SubaSic | 4
Czech Republic | Petr Cech | 3
England | Joe Hart | 4
France | Hugo Lloris | 7
Germany | Manuel Neuer | 6
Hungary | Gabor Kiraly | 4
Iceland | Hannes Halldorsson | 5
Italy | Gianluigi Buffon | 4
Italy | Salvatore Sirigu | 1
Northern Ireland | Michael McGovern | 4
Poland | Lukasz Fabianski | 4
Poland | Wojciech Szczesny | 1
Portugal | Rui Patricio | 7
Republic of Ireland | Darren Randolph | 4
Romania | Ciprian Tatarusanu | 3
Russia | Igor Akinfeev | 3
Slovakia | MatusKozacik | 4
Spain | David de Gea | 4
Sweden | Andreas Isaksson | 3
Switzerland | Yann Sommer | 4
Turkey | Volkan Babacan | 3
Ukraine | Andriy Pyatov | 3
Wales | Danny Ward | 1
Wales | Wayne Hennessey | 5
(27 rows)
Code Explanation:
The said query in SQL that retrieves the country name, player name, and count_gk columns from the match_details, soccer_country, and player_mast tables.
The JOIN clause joins the table match_details on team_id with soccer_country, and the table player_mast on player_gk with player_id column.
The query groups the data by country name and player name, and orders the data by country name, player name, and count_gk in descending order.
Alternative Solutions:
Using a Self-Join:
-- This query selects the country name, player name, and count of goalkeepers for each country, filtering out null goalkeeper entries.
SELECT
b.country_name, -- Selecting the country name from the soccer_country table aliased as 'b'
c.player_name, -- Selecting the player name from the player_mast table aliased as 'c'
COUNT(a.player_gk) count_gk -- Counting the occurrences of goalkeeper player IDs from the match_details table aliased as 'a'
FROM
match_details a -- Specifying the match_details table with an alias 'a'
JOIN
soccer_country b ON a.team_id = b.country_id -- Joining the soccer_country table with the match_details table based on the team_id
JOIN
player_mast c ON a.player_gk = c.player_id -- Joining the player_mast table with the match_details table based on the goalkeeper player ID
WHERE
a.player_gk IS NOT NULL -- Filtering out rows where goalkeeper player ID is not null
GROUP BY
b.country_name, c.player_name -- Grouping the results by country name and player name
-- Ordering the results by country name and player name, with goalkeeper count in descending order
ORDER BY
b.country_name, c.player_name, count_gk DESC;
Explanation:
This query includes a WHERE clause to filter out any rows where player_gk is null, ensuring only valid goalkeeper entries are counted.
Using a HAVING Clause:
-- This query selects the country name, player name, and count of goalkeepers for each country.
SELECT
b.country_name, -- Selecting the country name from the soccer_country table aliased as 'b'
c.player_name, -- Selecting the player name from the player_mast table aliased as 'c'
COUNT(a.player_gk) count_gk -- Counting the occurrences of goalkeeper player IDs from the match_details table aliased as 'a'
FROM
match_details a -- Specifying the match_details table with an alias 'a'
JOIN
soccer_country b ON a.team_id = b.country_id -- Joining the soccer_country table with the match_details table based on the team_id
JOIN
player_mast c ON a.player_gk = c.player_id -- Joining the player_mast table with the match_details table based on the goalkeeper player ID
GROUP BY
b.country_name, c.player_name -- Grouping the results by country name and player name
HAVING
COUNT(a.player_gk) > 0 -- Filtering out groups where goalkeeper count is greater than 0
ORDER BY
-- Ordering the results by country name and player name, with goalkeeper count in descending order
b.country_name, c.player_name, count_gk DESC;
Explanation:
This query applies the condition in the HAVING clause to filter out rows where there are no goalkeeper entries.
Go to:
PREV : 2nd highest stoppage time in the 2nd half of matches.
NEXT : Find the venue that has seen the most goals.
Practice Online
Sample Database: soccer
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.