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.