Why do I get the following error message in sqlite3
Error: near line 99: near ".": syntax error SELECT cast.cast_id, cast.cast_name, AVG(movies.score) FROM movies INNER JOIN cast ON cast.movie_id = movies.id WHERE movies.score >= 40 GROUP BY cast.cast_name HAVING COUNT(movies.id) >= 2 ORDER BY AVG(movies.score) DESC, cast.cast_name DESC LIMIT 10;
Line 99 is the first line (the SELECT line)
cast is a reserved word in SQLite. That makes it a really bad choice for a table or column name. The list of reserved words is in the documentation.
If you cannot change the table name, use explicit aliases:
SELECT c.cast_id, c.cast_name, AVG(m.score) FROM movies m INNER JOIN "cast" c ON c.movie_id = m.id WHERE m.score >= 40 GROUP BY c.cast_id, c.cast_name HAVING COUNT(m.id) >= 2 ORDER BY AVG(m.score) DESC, c.cast_name DESC;
- The table name is surrounded by escape characters (backticks and square braces also work).
GROUP BYhas all unaggregated columns in the
- Aliases are abbreviations for the table names.