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

# 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.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.

﻿

## SQL: Tips of the Day

What is the best way to paginate results in SQL Server?

```SELECT  *
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNum, *
FROM      Orders
WHERE     OrderDate >= '1980-01-01'
) AS RowConstrainedResult
WHERE   RowNum >= 1
AND RowNum < 20
ORDER BY RowNum
```

Database: SQL Server

Ref: https://bit.ly/3MGrNlk

We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook