September 15, 2013
Sort by AVG(rating)
Tompa’s Question:
I am trying to write a mySQL-query that sorts by first suburb and then AVG(rating_table.rating).
Here is the street_table:
id street_name suburb
0 streetone subone
1 streettwo subthree
2 streetthree subthree
3 streetfour subtwo
And here is the rating_table:
street_id rating
1 1
2 1
3 4
2 2
1 3
And this is the result I am looking for:
id suburb avarage_rating
0 subone (no rating)
1 subtwo 1 + 3 / 2 = 2
3 subthree 4 / 1 = 4 (Just one vote..)
2 subthree 2 + 1 / 2 = 1.5
(As you can see, #3 is before #2 because of the avarage_rating)
You can combine the ORDER BY to use multiple columns like:
SELECT .... ORDER BY suburb, AVG(rating_table.rating);
You can define order specific to items too
SELECT .... ORDER BY suburb ASC, AVG(rating_table.rating) DESC;
