Lesson 11.2

SQL GROUP BY Practice: Counting Movies by Genre

Roll the catalog up by genre with GROUP BY, COUNT, and AVG.

Listing films is useful, but a shop owner usually wants a summary: how deep is each genre, and how well does it rate? That is a job for GROUP BY.

Grouping collapses many rows into one row per distinct value. Once the rows are grouped by genre, an aggregate like count(*) or avg(rating) runs once per group.

Every selected column is grouped or aggregated

The rule that trips people up: any column in the SELECT list must either appear in GROUP BY or sit inside an aggregate. Here genre is grouped, while count(*) and avg(rating) are aggregates.

Pattern

select group_column,
       count(*) as row_count,
       round(avg(number_column), 2) as average
from table_name
group by group_column
order by row_count desc, group_column

Schema · Movie RentalsTable · movies5 columns · 12 rows

Table · movies

One row per movie in the catalog.

5 columns · 12 rows

movie_id inttitle textgenre textrelease_year intrating decimal

Your task

For each genre, return the genre, the number of films (movie_count), and the average rating rounded to two decimals (avg_rating). Sort by movie_count descending, then genre.

SQL Workbench

query.sqlmovie_rentals · SQL engine loading

⌘↵ to run

Expected answer