SQL Exercise: Find the venue that has seen the most goals

SQL soccer Database: Joins Exercise-25 with Solution

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

Sample table: soccer_country

Sample table: match_mast

Sample table: soccer_venue

Sample Solution:

SQL Code:

SELECT venue_name, count(venue_name)
FROM goal_details
JOIN soccer_country
ON goal_details.team_id=soccer_country.country_id
JOIN match_mast ON goal_details.match_no=match_mast.match_no
JOIN soccer_venue ON match_mast.venue_id=soccer_venue.venue_id
GROUP BY venue_name
HAVING COUNT (venue_name)=( 
SELECT MAX(mycount) 
FROM ( 
SELECT venue_name, COUNT(venue_name) mycount
FROM goal_details
JOIN soccer_country
ON goal_details.team_id=soccer_country.country_id
JOIN match_mast ON goal_details.match_no=match_mast.match_no
JOIN soccer_venue ON match_mast.venue_id=soccer_venue.venue_id
GROUP BY venue_name) 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.

Practice Online

Sample Database: soccer

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: How many games the goalkeeper played for his team?
Next SQL Exercise: Oldest player to have played in a EURO cup 2016 match.