mysql help

[c_pattle]

I have the following sql which works fine

 

"SELECT AVG(ratings.score), articles.* FROM ratings, articles where ratings.article_number = articles.article_number group by article_number"

 

However I want to add to this statement to make sure that it only returns the results where the field avg(ratings.score) is between 3 and 4.  I tried the sql below but it came up with the error "invalid use of group function". 

 

SELECT AVG(ratings.score), articles.* FROM ratings, articles where ratings.article_number = articles.article_number and AVG(ratings.score) < 4 group by article_number

 

Thanks for any help

[mikosiko]

article_number is ambiguous... you have that column in both tables... you must specify which one you want to use in your group by

[c_pattle]

Thanks.  I changed it to

 

SELECT AVG(ratings.score), articles.* FROM ratings, articles where ratings.article_number = articles.article_number and AVG(ratings.score) < 4 group by ratings.article_number;

 

However this is still give me to same problem.  Do you know what could be wrong?

[kickstart]

Hi

 

You are trying to check against the result of an aggregate function, so check using HAVING rather than WHERE.

 

All the best

 

Keith