Code: Select all
SELECT t.id, t.name, t.model, t.tons, t.tech, t.era, t.tro, t.new, AVG(rt.rating) AS avg_rating
FROM mbc_tro t
LEFT JOIN review_tro rt ON t.id = rt.mechid AND rt.status = 1
GROUP BY t.id
ORDER BY avg_rating DESC, t.name, t.modelThe intent of the SQL above is to GROUP by the ID (key field) and get an average rating. Getting the average rating works great. However, when I try to sort by the average, it always shows the designs with NO ratings first, and the ones that were reviewed last. For example, with the SQL above, it displays all the designs that have an avg_rating field of null. Then it will cycle through starting with 5.00 down to 0.00. If I change the order type to ASC, it STILL shows the null ones first followed by the 0.00 up to 5.00.
My question is, how do I get it so it is truly sorting by the rating first. I want to see the 5.00 ratings before I see the null ratings.
Any help would be appreciated!