sqlite mistake in otherwise working script

1971 views sql
5

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)

answered question

1 Answer

2

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;

Notes:

  • The table name is surrounded by escape characters (backticks and square braces also work).
  • The GROUP BY has all unaggregated columns in the SELECT.
  • Aliases are abbreviations for the table names.

posted this

Have an answer?

JD

Please login first before posting an answer.