MYSQL JOIN and AVG problem

Hello!!

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?

Greetings
Fabian Koenig

Try to use an alias for "AVG(b.rating)", like "AVG(b.rating) AS averages" and sort on that by using "ORDER BY averages".

thanks very much... it works great :)