Gerald Taylor wrote:
I have a table called ratings. It has 3 rows of interest
id foreign key to another table qual a quality rating u the user who gave that rating (also a foreign key into the users table)
Now I would like to select all the unique ids for which the average quality is below 1.5
so if we have id qual u 1 5 999 1 4 888 2 1 999 2 1 888 3 3 777 3 2 888
it would tell me that id 2 has average ratings below 1.5
it doesn't like SELECT id from ratings where AVG(qual) < 1.5 group by id
unfortch this server is still 3.23
The WHERE clause is used to determine which rows to look at in order to calculate the value of the aggregate function (AVG in this case), so you can't use the result of the calculation there. The HAVING clause, on the other hand, is used to filter rows near the end, after the calculations have been done. So, you need something like:
SELECT id FROM ratings GROUP BY id HAVING AVG(qual) < 1.5
If you want to actually see the averages, you'd change this to something like:
SELECT id, AVG(qual) AS average FROM ratings GROUP BY id HAVING average < 1.5
Michael
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
