The database is from CS50's introduction to Computer Science course, Psets 7 | Movies. https://cs50.harvard.edu/x/2022/psets/7/movies/
When I run this command:
SELECT AVG(rating) FROM ratings WHERE movie_id = (SELECT id FROM movies WHERE year = 2012);
It returns:
+-------------+
| avg(rating) |
+-------------+
| |
+-------------+
Though the query works for any value apart from 2012, for example:
sqlite> SELECT AVG(rating) FROM ratings WHERE movie_id = (SELECT id FROM movies WHERE year = 2013);
+-------------+
| avg(rating) |
+-------------+
| 7.3 |
+-------------+
The SUM and Queries like:
SELECT AVG(rating) FROM ratings WHERE
movie_id = (SELECT id FROM movies WHERE year > 2011 AND year < 2013);
returns the exact same Blank table from before and the COUNT returns 0.
+---------------+
| Count(rating) |
+---------------+
| 0 |
+---------------+
What am I doing wrong here? and do let me know if more info is required.
[EDIT]
An example of cross-referencing manually;
SELECT id, title, year FROM movies WHERE year = 2012 LIMIT 1;
+--------+-----------------------+------+
| id | title | year |
+--------+-----------------------+------+
| 383010 | The Three Stooges | 2012 |
+--------+-----------------------+------+
SELECT rating FROM ratings WHERE movie_id = 383010;
+--------+
| rating |
+--------+
| 5.1 |
+--------+
Table details:
CREATE TABLE movies (
id INTEGER,
title TEXT NOT NULL,
year NUMERIC,
PRIMARY KEY(id)
);
CREATE TABLE stars (
movie_id INTEGER NOT NULL,
person_id INTEGER NOT NULL,
FOREIGN KEY(movie_id) REFERENCES movies(id),
FOREIGN KEY(person_id) REFERENCES people(id)
);
CREATE TABLE directors (
movie_id INTEGER NOT NULL,
person_id INTEGER NOT NULL,
FOREIGN KEY(movie_id) REFERENCES movies(id),
FOREIGN KEY(person_id) REFERENCES people(id)
);
CREATE TABLE ratings (
movie_id INTEGER NOT NULL,
rating REAL NOT NULL,
votes INTEGER NOT NULL,
FOREIGN KEY(movie_id) REFERENCES movies(id)
);
CREATE TABLE people (
id INTEGER,
name TEXT NOT NULL,
birth NUMERIC,
PRIMARY KEY(id)
);
[EDIT 2] I guess I found the error, probably,
+--------+-----------------------+------+
| id | title | year |
+--------+-----------------------+------+
| 139613 | O Silêncio | 2012 |
+--------+-----------------------+------+
This ID among some others doesn't return a rating
sqlite> SELECT rating FROM ratings WHERE movie_id = 139613;
sqlite>
Perhaps that has got to do something with the error, nonetheless many movies do have a rating as shown previously. So, if that was the problem, then is there any way to solve it? So that I get the AVG rating with values in it ignoring the movies without a rating?
The problem with your query is that you are using the operator = to compare the movie_id with the result of the subquery:
SELECT id FROM movies WHERE year = 2012
In this case SQLite uses only one arbitrary id (which is the first of the resultset) from the table movies as the result of the subquery which may not have any rating.
Instead use the operator IN:
SELECT AVG(rating) FROM ratings WHERE movie_id IN (SELECT id FROM movies WHERE year = 2012);