Mysql – Find the total number of rentals for each genre

MySQL

The following are the tables in the database (removed irrelevant tables). I want to query for the total count of rentals for each genre. Note that a movie can have multiple genres.

MOVIE:  Table of movies that are available in the video store.
    id:             Integer identifying the movie instance.
    title:          String with the title of the movie.
    duration:       Integer with the total duration of the movie, in seconds.
    release_year:   Integer with the year of release of the movie.
    rental_rate:    Float with the daily cost of renting the movie.
    rating:         Float in the interval 0-10, measuring how much viewers 
                    enjoyed the movie.

GENRE:  Table of movie genres.
    id:             Integer identifying the genre instance.
    name:           String with the name of the genre.

RENTAL:  Table of video rentals.
    id:             Integer identifying the rental instance.
    customer_id:    Integer identifying the customer who performed the rental.
    movie_id:       Integer identifying the movie that was rented.
    checkout_date:  Integer with unix timestamp, in seconds, marking when 
                    the video was checked out by the customer.
    return_date:    Integer with unix timestamp, in seconds, marking when the 
                    video was returned by the customer.
    cost:           Float with total dollar amount that was charged to the 
                    customer for the rental.

MOVIE_GENRE:  Association between a movie and its genre.
    movie_id:       Integer identifying the movie instance.
    genre_id:       Integer identifying the genre instance.

What I have managed to do is simply count all the movies in each genre.

SELECT COUNT(mg.genre_id) AS movieCount, g.name
  FROM MOVIE_GENRE mg, GENRE g
  WHERE mg.genre_id = g.id
  GROUP BY mg.genre_id;

I'm doubting that this query even helps in what I'm trying to do. I know how I can achieve this programmatically, but I'm not sure how to express it in SQL.
We need to map each rental to a movie then to its genres, and count the genres we find here. How do I express this in SQL?

Best Answer

I've reduced a bit your table schema:

create table movie 
(
    id int primary key, 
    title varchar(20)
);

create table genre
(
    id int primary key, 
    name varchar(20)
);

create table movie_genre
(
    movie_id int, 
    genre_id int,
    primary key (movie_id, genre_id),
    foreign key (movie_id) references movie (id),
    foreign key (genre_id) references genre (id)
);

create table rental 
(
    id int primary key, 
    movie_id int
);

And then insert some values:

insert into movie values 
(1, 'movie 1'), (2, 'movie 2'), (3, 'movie 3'), (4, 'movie 4');

insert into genre values 
(1, 'genre 1'), (2, 'genre 2'), (3, 'genre 3'), (4, 'genre 4');

insert into movie_genre values
(1, 2), (1, 3),
(2, 1), (2, 2), (2, 4),
(3, 1), (3, 2), (3, 3), (3, 4),
(4, 2);

insert into rental values
(1, 1), (2, 2), (3, 1), (4, 3), (5, 1), (6, 3), (7, 4);

Now joining all tables and grouping by genre.name you can get the count of each genre:

select   g.name, count(*) rentals
from     rental r
join     movie  m
on       r.movie_id = m.id
join     movie_genre mg
on       mg.movie_id = m.id
join     genre g
on       mg.genre_id = g.id
group by g.name;

name    | rentals
:------ | ------:
genre 1 |       3
genre 2 |       7
genre 3 |       5
genre 4 |       3

dbfiddle here

Related Question