Why does SQLite3 return nothing when used to find the AVG, COUNT and SUM of this database?-CodePudding

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?

CodePudding user response：

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

CodePudding user response：

First 2 queries with "WHERE year = 2012" and "year = 2013" tell us there is no data at all for year 2012 in the table.

The next query with "HERE year > 2011 AND year < 2013" tries to select data where year less than 2013.

So, 2013 is being filtered out and is not to be displayed. All you need to do is to change the query to take year 2013 in to considetration:

AND year <= 2013