w3resource

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

soccer database relationship structure

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.

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

What is the best way to paginate results in SQL Server?

SELECT  *
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNum, *
          FROM      Orders
          WHERE     OrderDate >= '1980-01-01'
        ) AS RowConstrainedResult
WHERE   RowNum >= 1
    AND RowNum < 20
ORDER BY RowNum

Database: SQL Server

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

 





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