[SOLVED] How to work top averages out.

[Andrew R]

Hi

 

Struggling to get my head around this. Basically I have system where users can rate restaurants.  I have table containing restaurant info.  In another table I have user ratings.  What I am trying to do is calculate the average rating of each restaurant and then display the top 5 restaurants with the highest average. 

 

What do you recommend is the best way to do this?  Calculate the top five averages in user rating and then join it with the restaurant table?

 

Many thanks

 

[Maq]

Post your table structures.

[Andrew R]

Cheers

 

CREATE TABLE IF NOT EXISTS `resturants` (

  `resturant_id` int(11) NOT NULL AUTO_INCREMENT,

  `name` varchar(250) NOT NULL,

  `info` varchar(500) NOT NULL,

  `status` int(11) NOT NULL DEFAULT '1',

  PRIMARY KEY (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

 

CREATE TABLE IF NOT EXISTS `ratings` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `resturant_id` int(11) NOT NULL,

  `rating` int(11) NOT NULL,

  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

[Maq]

You spelled restaurants wrong.

 

SELECT re.name, AVG(ra.rating) AS rating FROM resturants re LEFT JOIN ratings ra ON re.resturant_id = ra.resturant_id ORDER BY rating DESC LIMIT 5;

 

I'm not sure if you can do an ORDER BY from an aggregate function.  If you can this should give you what you want.

[Andrew R]

Thanks a million!