The table details are as follows:
Movie ( mID, title, year, director )
English: There is a movie with ID number mID, a title, a release year, and a director.
Reviewer ( rID, name )
English: The reviewer with ID number rID has a certain name.
Rating ( rID, mID, stars, ratingDate )
English: The reviewer rID gave the movie mID a number of stars rating (1-5) on a certain ratingDate.
I need to write a query base on the following question:
2)Find the difference between the average rating of movies released before 1980 and the average rating of movies released after 1980. (Make sure to calculate the average rating for each movie, then the average of those averages for movies before 1980 and movies after. Don't just calculate the overall average rating before and after 1980.)
Any assistance will be great.
select avg(avgOld) - avg(avgNew) from ((SELECT AVG(avgStars) as avgOld from (SELECT r.mID, AVG(stars) AS avgStars FROM Rating as r inner join Movie as m on m.mID=r.mID where m.year<1980 GROUP BY r.mID)) from (SELECT AVG(avgStars) as avgOld from (SELECT r.mID, AVG(stars) AS avgStars FROM Rating as r inner join Movie as m on m.mID=r.mID where m.year<1980 GROUP BY r.mID))
answered Nov 07, 2011 at 12:48 AM