I have made this sql query to display the top 25 films that has the highest average score where the avg score must be higher than zero
SELECT films.*
FROM films
JOIN films_genres on films_genres.genre_id in (#{all_genres.join(' , ')})
WHERE films.id = films_genres.film_id and films.id !=2 AND count(films_genres.score) > 0
GROUP BY films.id
ORDER BY count(films_genres.film_id) DESC"
Now I wonder if it's possible to display that average score of a film.
So I see something like this:
Film_ 1 3.56
Film_2 3.50
I use sqlite version 3 at the moment because I have to use this query in Rails.
Typically it's just an AVG function in ANSI-standard queries:
SELECT films.* , AVG(films_genres.score) AS avg_score
FROM films
INNER JOIN films_genres on films_genres.genre_id in (#{all_genres.join(' , ')})
WHERE films.id = films_genres.film_id AND
films.id !=2 AND
count(films_genres.score) > 0
GROUP BY films.id
ORDER BY count(films_genres.film_id) DESC"
SELECT films.names , AVG(films_genres.film_id) as average
FROM films
JOIN films_genres on films_genres.genre_id in (#{all_genres.join(' , ')})
WHERE films.id = films_genres.film_id and films.id !=2
GROUP BY films.id
ORDER BY count(films_genres.film_id) DESC"
but then I only see this :
#<Film:0x0000000555f3e8 id: nil, title: "Rerum quas maxime et cupiditate sed voluptatem numquam.">,
so no every score to see.
I use it in a ruby . find_by_sql function , can that be the problem?