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
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;
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)
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.
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
How to drop all tables from a database with one SQL query?
USE Databasename SELECT 'DROP TABLE [' + name + '];' FROM sys.tables
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- 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
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