Season Attendance at Each Football Stadium — SQL Problem
The league's commercial team is reviewing how full each ground was this season.
- Difficulty: Easy
- Topics: Aggregation
- Dialect: MySQL
- Problem: #453
Problem statement
The league's commercial team is reviewing how full each ground was this season. A fixture played behind closed doors has NULL attendance; it still counts as a match played at that stadium.
Return one row per stadium with the columns stadium, matches (every fixture played there) and total_attendance (the sum of the recorded crowds, 0 when none was recorded). Order the rows by total_attendance descending, then by stadium.
Tables
Table: StadiumFixture
| Column | Type |
|---|---|
| match_id | int |
| stadium | varchar |
| match_date | date |
| attendance | int |
Primary key: match_id.
One row per fixture played. attendance is NULL for a match behind closed doors.
Examples
Example 1
StadiumFixture
| match_id | stadium | match_date | attendance |
|---|---|---|---|
| 1 | Salt Lake Bowl | 2024-10-05 | 41200 |
| 2 | Fort Ground | 2024-10-06 | 18500 |
| 3 | Salt Lake Bowl | 2024-10-19 | NULL |
| 4 | Coastal Arena | 2024-10-20 | NULL |
| 5 | Fort Ground | 2024-11-02 | 22700 |
| 6 | Hillside Park | 2024-11-03 | 40000 |
| 7 | Salt Lake Bowl | 2024-11-16 | 38900 |
Output
| stadium | matches | total_attendance |
|---|---|---|
| Salt Lake Bowl | 3 | 80100 |
| Fort Ground | 2 | 41200 |
| Hillside Park | 1 | 40000 |
| Coastal Arena | 1 | 0 |
How to solve Season Attendance at Each Football Stadium
Grouping by stadium gives one row per ground; two aggregates fill it in. The point of the exercise is how each aggregate treats NULL.
COUNT(*) counts rows, so a closed-door fixture counts as a match — as the statement requires. COUNT(attendance) would skip it, because counting a column ignores its NULLs. SUM(attendance) also ignores NULLs, which is what we want for the total, but a stadium whose every fixture was behind closed doors has nothing to add up, and SQL returns NULL for the sum of an empty set, not 0. COALESCE(SUM(attendance), 0) turns that into 0; summing IFNULL(attendance, 0) instead, as the alternative does, gets the same result by replacing the NULLs before adding.
The ordering puts the biggest crowds first, and the stadium name settles equal totals (two closed-door grounds both at 0, for instance). One scan, one grouping.
Reference solution (MySQL)
SELECT stadium,
COUNT(*) AS matches,
COALESCE(SUM(attendance), 0) AS total_attendance
FROM StadiumFixture
GROUP BY stadium
ORDER BY total_attendance DESC, stadiumAnother way
SELECT stadium, COUNT(match_id) AS matches, SUM(IFNULL(attendance, 0)) AS total_attendance FROM StadiumFixture GROUP BY stadium ORDER BY SUM(IFNULL(attendance, 0)) DESC, stadium← Overseas Players in the Auction Pool by Role · T20 League Fixtures Played on a Weekend in 2024 →