CODEWITHSUNDEEP
mysql
Tompina
Tompina

Reputation: 777

Sort by AVG(rating)

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)

Upvotes: 1

Views: 1327

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269583

This is a join with aggregation. However, you need a left join to be sure that you keep all rows where there is no rating:

select s.id as street_id, s.suburb, avg(r.rating) as rating
from street_table s left join
     rating_table r
     on s.id = r.street_id
group by s.id, s.suburb
order by s.suburb, avg(r.rating) desc

Upvotes: 2

Starx
Starx

Reputation: 78971

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;

Upvotes: 0

Related Questions