SQL Exercise: How many games the goalkeeper played for his team?

SQL soccer Database: Joins Exercise-24 with Solution

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

Sample table: match_details

Sample table: soccer_country

Sample Solution:

SQL Code:

SELECT b.country_name,c.player_name,COUNT(a.player_gk) count_gk
FROM match_details a
JOIN soccer_country b ON a.team_id=b.country_id
JOIN player_mast c ON a.player_gk=c.player_id
GROUP BY b.country_name,c.player_name
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.

Practice Online

Sample Database: soccer

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: 2nd highest stoppage time in the 2nd half of matches.
Next SQL Exercise: Find the venue that has seen the most goals.