SQL Exercise: Find the venue that has seen the most goals
25. From the following tables, write a SQL query to find the venue where the most goals have been scored. Return venue name, number of goals.
Sample table: goal_details
goal_id | match_no | player_id | team_id | goal_time | goal_type | play_stage | goal_schedule | goal_half
---------+----------+-----------+---------+-----------+-----------+------------+---------------+-----------
1 | 1 | 160159 | 1207 | 57 | N | G | NT | 2
2 | 1 | 160368 | 1216 | 65 | P | G | NT | 2
3 | 1 | 160154 | 1207 | 89 | N | G | NT | 2
4 | 2 | 160470 | 1221 | 5 | N | G | NT | 1
5 | 3 | 160547 | 1224 | 10 | N | G | NT | 1
6 | 3 | 160403 | 1218 | 61 | N | G | NT | 2
7 | 3 | 160550 | 1224 | 81 | N | G | NT | 2
8 | 4 | 160128 | 1206 | 73 | N | G | NT | 2
9 | 4 | 160373 | 1217 | 93 | N | G | ST | 2
.....
108 | 51 | 160319 | 1214 | 109 | N | F | ET | 2
Sample table: soccer_country
country_id | country_abbr | country_name
------------+--------------+---------------------
1201 | ALB | Albania
1202 | AUT | Austria
1203 | BEL | Belgium
1204 | CRO | Croatia
1205 | CZE | Czech Republic
1206 | ENG | England
1207 | FRA | France
1208 | GER | Germany
1209 | HUN | Hungary
.......
1229 | NOR | Norway
Sample table: player_mast
player_id | team_id | jersey_no | player_name | posi_to_play | dt_of_bir | age | playing_club
-----------+---------+-----------+-------------------------+--------------+------------+-----+---------------------
160001 | 1201 | 1 | Etrit Berisha | GK | 1989-03-10 | 27 | Lazio
160008 | 1201 | 2 | Andi Lila | DF | 1986-02-12 | 30 | Giannina
160016 | 1201 | 3 | Ermir Lenjani | MF | 1989-08-05 | 26 | Nantes
160007 | 1201 | 4 | Elseid Hysaj | DF | 1994-02-20 | 22 | Napoli
160013 | 1201 | 5 | Lorik Cana | MF | 1983-07-27 | 32 | Nantes
160010 | 1201 | 6 | Frederic Veseli | DF | 1992-11-20 | 23 | Lugano
160004 | 1201 | 7 | Ansi Agolli | DF | 1982-10-11 | 33 | Qarabag
160012 | 1201 | 8 | Migjen Basha | MF | 1987-01-05 | 29 | Como
160017 | 1201 | 9 | Ledian Memushaj | MF | 1986-12-17 | 29 | Pescara
........
160548 | 1224 | 23 | Simon Church | FD | 1988-12-10 | 27 | MK Dons
Sample table: soccer_venue
venue_id | venue_name | city_id | aud_capacity
----------+-------------------------+---------+--------------
20001 | Stade de Bordeaux | 10003 | 42115
20002 | Stade Bollaert-Delelis | 10004 | 38223
20003 | Stade Pierre Mauroy | 10005 | 49822
20004 | Stade de Lyon | 10006 | 58585
20005 | Stade VElodrome | 10007 | 64354
20006 | Stade de Nice | 10008 | 35624
20007 | Parc des Princes | 10001 | 47294
20008 | Stade de France | 10002 | 80100
20009 | Stade Geoffroy Guichard | 10009 | 42000
20010 | Stadium de Toulouse | 10010 | 33150
Sample Solution:
SQL Code:
-- This query counts the occurrences of each venue name where goals were scored, and finds the venue(s) with the maximum count.
SELECT
venue_name, -- Selecting the venue name
COUNT(venue_name) -- Counting the occurrences of each venue name
FROM
goal_details -- Specifying the goal_details table
JOIN
soccer_country ON goal_details.team_id = soccer_country.country_id -- Joining the soccer_country table with the goal_details table based on the team_id
JOIN
match_mast ON goal_details.match_no = match_mast.match_no -- Joining the match_mast table with the goal_details table based on the match number
JOIN
soccer_venue ON match_mast.venue_id = soccer_venue.venue_id -- Joining the soccer_venue table with the match_mast table based on the venue_id
GROUP BY
venue_name -- Grouping the results by venue name
HAVING
COUNT(venue_name) = ( -- Filtering out groups where the count of venue names equals
SELECT
MAX(mycount) -- The maximum count of venue names
FROM
( -- Subquery to calculate the counts of venue names
SELECT
venue_name, -- Selecting the venue name
COUNT(venue_name) mycount -- Counting the occurrences of each venue name and aliasing it as 'mycount'
FROM
goal_details -- Specifying the goal_details table
JOIN
soccer_country ON goal_details.team_id = soccer_country.country_id -- Joining the soccer_country table with the goal_details table based on the team_id
JOIN
match_mast ON goal_details.match_no = match_mast.match_no -- Joining the match_mast table with the goal_details table based on the match number
JOIN
soccer_venue ON match_mast.venue_id = soccer_venue.venue_id -- Joining the soccer_venue table with the match_mast table based on the venue_id
GROUP BY
venue_name -- Grouping the results by venue name
) gd -- Aliasing the subquery as 'gd'
);
Sample Output:
venue_name | count -----------------+------- Stade de France | 18 (1 row)
Code Explanation:
The said query in SQL that retrieves the name of the venue(s) where the maximum number of goals have been scored in a football tournament.
The JOIN statements are used to join the goal_details and soccer_country tables based on the team_id and country_id column, the goal_details and match_mast tables based on the match_no column, and the match_mast and soccer_venue tables based on the venue_id column.
The GROUP BY clause groups the results by venue_name, which means that the count of goals scored in each venue will be aggregated.
The HAVING clause is used to filter the results based on the maximum number of goals scored in a venue. Only the venues that have the maximum count of goals scored will be selected.
Alternative Solution:
Using a Self-Join:
-- This query counts the occurrences of each venue name where goals were scored, and finds the venue(s) with the maximum count.
SELECT
venue_name, -- Selecting the venue name
COUNT(venue_name) as count -- Counting the occurrences of each venue name and aliasing it as 'count'
FROM
goal_details -- Specifying the goal_details table
JOIN
soccer_country ON goal_details.team_id = soccer_country.country_id -- Joining the soccer_country table with the goal_details table based on the team_id
JOIN
match_mast ON goal_details.match_no = match_mast.match_no -- Joining the match_mast table with the goal_details table based on the match number
JOIN
soccer_venue ON match_mast.venue_id = soccer_venue.venue_id -- Joining the soccer_venue table with the match_mast table based on the venue_id
GROUP BY
venue_name -- Grouping the results by venue name
HAVING
COUNT(venue_name) = ( -- Filtering out groups where the count of venue names equals
SELECT
MAX(mycount) -- The maximum count of venue names
FROM
( -- Subquery to calculate the counts of venue names
SELECT
COUNT(venue_name) as mycount -- Counting the occurrences of each venue name and aliasing it as 'mycount'
FROM
goal_details -- Specifying the goal_details table
JOIN
soccer_country ON goal_details.team_id = soccer_country.country_id -- Joining the soccer_country table with the goal_details table based on the team_id
JOIN
match_mast ON goal_details.match_no = match_mast.match_no -- Joining the match_mast table with the goal_details table based on the match number
JOIN
soccer_venue ON match_mast.venue_id = soccer_venue.venue_id -- Joining the soccer_venue table with the match_mast table based on the venue_id
GROUP BY
venue_name -- Grouping the results by venue name
) as subquery -- Aliasing the subquery as 'subquery'
);
Explanation:
This query uses a subquery in the FROM clause to first aggregate the data by venue_name, then applies the outer query's grouping and having conditions.
Go to:
PREV : How many games the goalkeeper played for his team?
NEXT : Oldest player to have played in a EURO cup 2016 match.
Practice Online
Sample Database: soccer
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.