SQL Exercise: Goals scored by players based on their position
SQL soccer Database: Joins Exercise-60 with Solution
60. From the following table, write a SQL query to find the goal scored by each player according to their position. Return country name, position to play, and number of goals.
Sample table: goal_details
Sample table: player_mast
Sample table: soccer_country
Sample Solution:
SQL Code:
SELECT country_name,
posi_to_play,
count(*) AS "Number of goals"
FROM goal_details a
JOIN player_mast b ON a.player_id=b.player_id
JOIN soccer_country c ON a.team_id=c.country_id
GROUP BY country_name,
posi_to_play
ORDER BY country_name;
Sample Output:
country_name | posi_to_play | Number of goals ---------------------+--------------+----------------- Albania | FD | 1 Austria | MF | 1 Belgium | DF | 1 Belgium | FD | 3 Belgium | MF | 5 Croatia | FD | 1 Croatia | MF | 4 Czech Republic | FD | 2 England | FD | 3 England | MF | 1 France | FD | 9 France | MF | 4 Germany | DF | 1 Germany | FD | 3 Germany | MF | 3 Hungary | FD | 4 Hungary | MF | 1 Iceland | DF | 2 Iceland | FD | 4 Iceland | MF | 3 Italy | DF | 2 Italy | FD | 3 Italy | MF | 1 Northern Ireland | DF | 2 Northern Ireland | FD | 1 Poland | FD | 2 Poland | MF | 2 Portugal | FD | 8 Portugal | MF | 1 Republic of Ireland | DF | 1 Republic of Ireland | MF | 3 Romania | FD | 2 Russia | DF | 1 Russia | MF | 1 Slovakia | MF | 3 Spain | DF | 1 Spain | FD | 4 Switzerland | DF | 1 Switzerland | FD | 1 Switzerland | MF | 1 Turkey | FD | 1 Turkey | MF | 1 Wales | DF | 2 Wales | FD | 6 Wales | MF | 1 (45 rows)
Code Explanation:
The given query in SQL that returns a list of countries and positions to play, along with the number of goals scored for each combination. The results are grouped by country and position, and sorted by country name.
The JOIN clause combines the goal_details and player_mast tables based on the player_id column and goal_details and soccer_country tables based on the team_id and country_id columns.
The GROUP BY clause is used to aggregate data based on one or more columns.
The result set groups by the country name and position to play columns.
This sorts the results by country name in alphabetical order.
Practice Online
Sample Database: soccer

Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous SQL Exercise: Find the results of penalty shootout matches.
Next SQL Exercise: Players who entered the field in the most recent play.
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
- Weekly Trends
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
- JavaScript functions Exercises
- 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