SQLTeam.com | Weblogs | Forums

Can the average of the score of a film also be displayed (sqlite)


#1

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.

Regards,

Roelof


#2

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"

#3

Haven't tried it, but I'm surprised that you can get away with a COUNT on the JOINed sub table in the WHERE (rather than a HAVING).


#4

I tried it yet with this code :

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?


#5

If you want to list the film name, then you'll need to translate the name after grouping by film_id first.

And why are you averaging the ID? That's not logical.