MYSQL JOIN and AVG problem
I have a prob with Mysql.
I got two tables
First : material with fields 'id' and 'name'
Second: ratings with fields 'material_id', 'comment' and 'rating'
Now I want to select all material with the average rating associated.
So I did
"SELECT m.id,AVG(b.rating) FROM material as m LEFT JOIN bewertungen as b ON m.id = b.materialid AND b.rating > 0 GROUP BY id" .
It works so far as it returns all material_ids associated with the average rating for this material or NULL if no rating is present in the ratings table.
Now I want to sort the results by AVG(rating) but i cannot add "ORDER BY AVG(rating)" as this results in an error.
Can anyone help me, please?