Assignment Query Desk
uses LEFT JOIN, GROUP BY, HAVING, COUNT, and AVG to show which countries host the strongest attendance environments.
Query brief
Countries averaging at least 10,000 fans per game.
capture the result grid in phpMyAdmin after running the query.
SELECT
c.country_name,
COUNT(DISTINCT t.team_id) AS clubs,
ROUND(AVG(ts.avg_attendance), 0) AS avg_attendance,
ROUND(AVG(ts.wins), 1) AS avg_wins
FROM countries c
LEFT JOIN teams t ON t.country_id = c.country_id
LEFT JOIN team_seasons ts ON ts.team_id = t.team_id
GROUP BY c.country_id, c.country_name
HAVING AVG(ts.avg_attendance) >= 10000
ORDER BY avg_attendance DESC, clubs DESC;Live result
| Country Name | Clubs | Avg Attendance | Avg Wins |
|---|---|---|---|
| Serbia | 1 | 15326 | 8.5 |
| Lithuania | 1 | 13776 | 5.9 |
| Turkey | 1 | 11676 | 17.7 |
| Greece | 1 | 10400 | 23.2 |
| Germany | 1 | 10047 | 4.4 |