How to display top 50 average ratings

alexvgtb

Hello,

Maybe you could help me... I'm a bit stuck.
I'm working on a script to rate restaurants.
I have 2 different tables

Here's my table 1 :

I have these fields :

id
name (of restaurant)

Here's my table 2 :

id
restaurant_id (same as id of table 1)
rating

Now I want to display a page with the top 50 restaurants
Like this :

Name_of_restaurant : average rating for this restaurant

Do you see the problem? Two different tables... need to average numbers from one table.

Thanks for helping me if you can.

Alex

Norman_Graham

Hi Alex

Is there any reason to have two tables here? I recommend one table with id / name / rating.

MySQL provides commands for averaging field values. Try this manual page.

Note that you need MySQL 4.1 to do subqueries. I assume your ratings are integers or floats.

HTH

Norm

Roger_Ramjet

I'm sure you mean that you have a table that stores multiple ratings for each restaurant, though this is not clear from your post.

To return the avaerage of the ratings you need to use GROUP BY and the aggregate functions


SELECT r.name,  AVG(x.rating) as  rating
FROM restaurants as r INNER JOIN ratings as x ON r.id = x.restaurant_id
GROUP BY r.name, x.rating
ORDER BY x.rating
LIMIT 50

alexvgtb

Exactly Roger!

Thanks to you both for your help...

I'll try the aggregate functions.