w3resource

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

soccer database relationship structure

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.



Follow us on Facebook and Twitter for latest update.

SQL: Tips of the Day

How to drop all tables from a database with one SQL query?

USE Databasename

SELECT  'DROP TABLE [' + name + '];'
FROM    sys.tables

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

 





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