MYSQL - Generating an average rating
Posted on 2012-09-05
I'm working on an API with two tables.
Salons - Contains a list of salons and their locations
Comments - Contains comments for each salon along with a rating for each comment
Currently I've got the following SQL query which returns back a list of salons based on location.
"SELECT *, 6371.04 * acos( cos( pi( ) /2 - radians( 90 - latitude) ) * cos( pi( ) /2 - radians( 90 - '$lat' ) ) * cos( radians( longitude) - radians( '$lon' ) ) + sin( pi( ) /2 - radians( 90 - latitude) ) * sin( pi( ) /2 - radians( 90 - '$lat' ) ) ) AS distance FROM cms_salon GROUP BY salon_id HAVING distance < '$radius' ORDER BY distance ASC"
What I want to do now is within that query, get a list of all comments for each salon and generate an average rating and then return the average rating as part of the results.
Is that possible?
The table layouts are;