I'm trying to extract the average rating for each movie genre (only for ratings made in December). I first joined two tables to have the necessary information. Then I converted the timestamp into the yyyy-mm-dd format. Everything at this point was working but then I tried to calculate and display the average rating for each genre and the server identifies an error.
This is my code:
SELECT AVG(rating) AS AvgRating, genres, timestamp,
FROM_UNIXTIME(timestamp) as datetime,
CAST(FROM_UNIXTIME(timestamp) as date) AS date_value
FROM ratings
INNER JOIN movies
ON ratings.movieId=movies.movieId
WHERE month(CAST(FROM_UNIXTIME(timestamp) as date) )=12
GROUP BY genres;
The error comes from the AVG or GROUP BY function but I don't understand what is wrong.
Your help will be very appreciated!
