Get Average in SQL Through Join

Question

I'm just playing around with SQL and I'm trying to do the following.

I have 2 tables and here is their structure:

1. Movies_metadata Movies

2. ratings table: Ratings

As there are many ratings for one movie, what I'd like to do is get the avg rating per movie and have it display next to the title which is only available in the Metadata table.

This is as far as I got but obviously the issue with my SELECT statement is that it'll return the average of all movies and display it for each record:

SELECT
    (SELECT 
        AVG(rating)
    FROM 
        `movies-dataset.movies_data.ratings`) AS rating_avg,
    metadata.title,
    metadata.budget,
    metadata.revenue,
    metadata.genres,
    metadata.original_language,
    metadata.release_date
FROM 
    `movies-dataset.movies_data.Movies_metadata` AS metadata
INNER JOIN `movies-dataset.movies_data.ratings` AS ratings
    ON metadata.id = ratings.movieId
LIMIT 10

Here is an example of the result:

Result

I'm thinking I can potentially use a GROUP BY but when I try, I get an error

Appreciate the help!

Answer 1

The following should work:

SELECT movies_metadata.title, AVG(ratings.rating)
FROM movies_metadata
LEFT JOIN ratings ON movies_metadata.id = ratings.movieID
GROUP BY movies_metadata.title

You can swap movies_metadata.title by movies_metadata.id if not unique.