w3resource

SQL Exercise: Countries where assistant referees assist most matches

SQL soccer Database: Joins Exercise-47 with Solution

47. From the following table, write a SQL query to find the countries from where the assistant referees assist most of the matches. Return country name and number of matches.

Sample table: match_details


Sample table: asst_referee_mast


Sample table: soccer_country


Sample Solution:

SQL Code:

SELECT country_name,
       count(DISTINCT match_no)
FROM match_details a
JOIN asst_referee_mast c ON a.ass_ref=c.ass_ref_id
JOIN soccer_country b ON c.country_id=b.country_id
GROUP BY country_name
HAVING count(DISTINCT match_no)=
  (SELECT max(mm)
   FROM
     (SELECT count(DISTINCT match_no) mm
      FROM match_details a
      JOIN asst_referee_mast c ON a.ass_ref=c.ass_ref_id
      JOIN soccer_country b ON c.country_id=b.country_id
      GROUP BY country_name) hh);

Sample Output:

 country_name | count
--------------+-------
 England      |     7
(1 row)

Code Explanation:

The said query in SQL that selects the name of countries and the count of distinct match numbers in the match_details table for each country.
The HAVING clause filters the results to only show the countries whose count of distinct match numbers is equal to the maximum count of distinct match numbers among all countries. The maximum count of distinct match numbers is obtained by a subquery that selects the count of distinct match numbers for each country and then selects the maximum count from those results.
The JOIN clause joins the match_details and asst_referee_mast table based on the ass_ref and ass_ref_id columns, joins the results with the soccer_country tables based on the country_id column to obtain the required data.

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: Assistant referees of each countries assists matches.
Next SQL Exercise: List the referees with their countries for each 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