StackOverflow - Solved

looking for some solutions? You are welcome.

SOLVED: SQL Stanford Mini Course - Difference between average Rating of Movies: logic behind problem + no such table error

Maggie Liu:

Question:

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.)

Note: This is in SQL Lite.

Schema:

``````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.

Below is my attempt at solving this. Here is my logic and breakdown of my mess of a query.

1. The question above says (Make sure to calculate the average rating for each movie and THEN the average of those averages for movies before 1980 and movies after). For this part of the query below, I sought to create a table of all the average ratings of every movie. I know I want to reference those Averages later as I need to average them AGAIN so I want to create a Table I can use later which is why I chose to put a subquery in the From clause.

`(SELECT avg(Stars) as avgStars from Rating) as Avg1,`

2. I then reference the table I tried to create above as I want to average those original averages and then separate the movies by year. This query below is Movies released before 1980. I know I want to reference this later as well in the select clause so I rename it Pre1980.

`(SELECT avg(avgStars) from Avg1 where mID in (SELECT mID from Movie where year < 1980)) as Pre1980,`

1. Finally, I create an average of the original averages in Step 1 for the movies released after 1980 and rename it so I can reference it again in the Select Clause.

`(SELECT avg(avgStars) from Avg1 where mID in (SELECT mID from Movie where year > 1980)) as Post1980;`

1. My last step is the difference between the average ratings of movies released before 1980 and the average of ratings of movies released after 1980.

`SELECT Post1980.AvgStars - Pre1980.AvgStars`

Attempted Solution

``````SELECT Post1980.AvgStars - Pre1980.AvgStars
FROM
(SELECT avg(Stars) as avgStars from Rating) as Avg1,
(SELECT avg(avgStars) from Avg1 where mID in
(SELECT mID from Movie where year < 1980)) as Pre1980,
(SELECT avg(avgStars) from Avg1 where mID in
(SELECT mID from Movie where year > 1980)) as Post1980;
```
```

My Problem

I get an error says `Query Failed to execute: no such table: Avg 1`. Is there something wrong that I'm doing in my steps? In addition, I feel like I'm making more mistakes than just the renaming so if there are any mistakes in my logic please help! I would love to learn the logic behind this query.

Posted in S.E.F
via StackOverflow & StackExchange Atomic Web Robots