Filter AVG in long query

[stone28]

Hi All,

I have a query which works like I want but it calculates AVG in the wrong way.

SELECT tbl_movies.movie_id, tbl_movies.movie_title, tbl_movies.movie_year, tbl_movies.movie_imdb, COUNT(tbl_users_comments.movie_id) AS 'users comments', AVG(tbl_users_comments.score) AS 'users average'

FROM tbl_movies "&_
​

LEFT JOIN tbl_users_comments ON tbl_movies.movie_id = tbl_users_comments.movie_id

GROUP BY tbl_movies.movie_id, tbl_movies.movie_title, tbl_movies.movie_year, tbl_movies.movie_imdb

ORDER by tbl_movies.movie_title;


​

However, the AVG is calculated from all fields and if there is no value in the field (type Float) then the default there is 0 which of course I don't want to count. Is it possible that I can squeeze somewhere WHERE tbl_users_comments.score>0 - but only for that one field. I don't want to filter everything. It's a long table of movies and some of them will have average score, some of them won't, but I want to list them all.

Please help or ask any questions if it's not clear what I wrote here.

Thank you.

 

[DCrake]

What are you trying to average?

 

[stone28]

table tbl_users_comments has a score field. So everytime somebody wants to leave a comment they can but they can also add their score. Unfortunatelly when they leave just a comment the score field is being field with 0. So then whay I try to calculate the average score for each movie that field is being counted as well. But I want to count only avg from scores > 0.

 

[DCrake]

Then in the field that user should be adding their score you need to enter a condition > 0 Or Is Null

So only records that have a score will averaged.

 

[stone28]

Thanks, The problem is that I use the DB from the web interface..... I believe you are talking about doing it in access?