Averaging Movie Ratings in SQL

I have a SQL data base comparing different ratings that critics give to movies. I am trying to find the average of the critics' ratings for each unique movie and then create a new table to display these aggregated ratings.

I tried using the AVG(DISTINCT()) functionality, but it only found the averages by distinct ratings, not by distinct movies. Any help with aggregation would be huge!

ETA: Sample data example

Movie Rating
Jaws 1
Jaws 4
Jaws 3
HomeAlone 2
HomeAlone 5

Returned data example

Movie Rating
Jaws 2.67
HomeAlone 3.5

So average all of the individual ratings by the movie name and then create a new table.

2 answers

  • answered 2021-07-19 21:21 L. Carpentier

    A possible solution is to group by Movie.

    SELECT Movie, AVG(Rating) FROM [Table] GROUP BY Movie;
    

  • answered 2021-07-20 07:47 sama latifi

    I think this will create the answer you want as the output in a new table

    CREATE TABLE new_table AS 
    SELECT Movie, round(AVG(Rating),2) 
    FROM table_name
    GROUP BY Movie;
    

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum