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
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;
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)
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.
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
- Weekly Trends
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
- Python Tutorial
- Python Array Exercises
- SQL Cross Join
- C# Sharp Array Exercises
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