asked
SQL query help..
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: 1.) For each movie, return the title and the 'rating spread', that is, the difference between highest and lowest ratings given to that movie. Sort by rating spread from highest to lowest, then by movie title. 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.
As very similar question has already been answered once yesterday [here][1]: I suspect a homework tag needs to be added to the post... [1]:
http://ask.sqlservercentral.com/questions/80494/to-write-an-sql-query
Enjoy !) 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))
SELECT ( SELECT AVG(oldiestar) AS ldiestar FROM ( SELECT Movie.title AS oldietitle , AVG(Rating.stars) AS oldiestar FROM Rating INNER JOIN Reviewer ON Rating.rID = Reviewer.rID INNER JOIN Movie ON Rating.mID = Movie.mID GROUP BY Movie.title HAVING Movie.year < 1980 ) ) - ( SELECT AVG(newstar) AS ewstar FROM ( SELECT Movie.title AS newtitle , AVG(Rating.stars) AS newstar FROM Rating INNER JOIN Reviewer ON Rating.rID = Reviewer.rID INNER JOIN Movie ON Rating.mID = Movie.mID GROUP BY Movie.title HAVING Movie.year > 1980 ) )
1 Person is following this question.
