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?