sql - How do I specify that my condition is numeric. sqlite3

Question

I have this query here that is supposed to compute the average ratings from a table called ratings which have a movie_id and a rating columns. but i have to compute the average of just the movies released in 2012. and i have to compute the average in table name of my choosing so i picked avgg that has num which a text that i will write anything into and rat which is the average of ratings. i think my problem is that my code doesn't compare 2012 as a numeric value. here is the query.

CREATE TABLE avgg(num TEXT, rat INTEGER)
INSERT INTO avgg(num)
VALUES('The average is')
INSERT INTO avgg(rat)
SELECT avg(rating)
FROM ratings 
WHERE movie_id = (SELECT id FROM movies WHERE year = 2012)

question from:https://stackoverflow.com/questions/65835960/how-do-i-specify-that-my-condition-is-numeric-sqlite3

Answer 1

The problem is the syntax you use.
You can insert the row with the columns num and rat in a single INSERT statement:

INSERT INTO avgg(num, rat)
SELECT 'The average is', AVG(rating)
FROM ratings 
WHERE movie_id IN (SELECT id FROM movies WHERE year = '2012');

Or with a join:

INSERT INTO avgg(num, rat)
SELECT 'The average is', AVG(r.rating)
FROM ratings r INNER JOIN movies m
ON m.id = r.movie_id 
WHERE m.year = '2012';