MYSQL JOIN and AVG problem

[koenig]

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

[mordred]

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

[koenig]

thanks very much... it works great