Season Attendance at Each Football Stadium

Easy
453.

Season Attendance at Each Football Stadium

EasyAggregation

MySQL

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

ColumnType
match_idint
stadiumvarchar
match_datedate
attendanceint

Primary key: match_id.

One row per fixture played. attendance is NULL for a match behind closed doors.

Example

The rows must come back in the order shown.

StadiumFixture

match_idstadiummatch_dateattendance
1Salt Lake Bowl2024-10-0541200
2Fort Ground2024-10-0618500
3Salt Lake Bowl2024-10-19NULL
4Coastal Arena2024-10-20NULL
5Fort Ground2024-11-0222700
6Hillside Park2024-11-0340000
7Salt Lake Bowl2024-11-1638900

Output

stadiummatchestotal_attendance
Salt Lake Bowl380100
Fort Ground241200
Hillside Park140000
Coastal Arena10
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
match_idint
stadiumvarchar
match_datedate
attendanceint

Primary key: match_id.

One row per fixture played. attendance is NULL for a match behind closed doors.

Examples

Example 1

StadiumFixture

match_idstadiummatch_dateattendance
1Salt Lake Bowl2024-10-0541200
2Fort Ground2024-10-0618500
3Salt Lake Bowl2024-10-19NULL
4Coastal Arena2024-10-20NULL
5Fort Ground2024-11-0222700
6Hillside Park2024-11-0340000
7Salt Lake Bowl2024-11-1638900

Output

stadiummatchestotal_attendance
Salt Lake Bowl380100
Fort Ground241200
Hillside Park140000
Coastal Arena10

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, stadium

Another 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 →