Solved

# MYSQL - Generating an average rating

Posted on 2012-09-05
Medium Priority
574 Views
Hi,

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;

cms_salon
- salon_id
- salon_name
- longitude
- latitude

- id
- text
- name
- rating
- salon_id
0
Question by:SheppardDigital
• 3
• 3

LVL 60

Accepted Solution

Julian Hansen earned 2000 total points
ID: 38367187
I assume when you say
get a list of all comments
You are referring to getting all comment records to generate the average - not actually return the comments themselves?
Assume the above is correct then you could try this
``````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, a.average_rating
FROM cms_salon s LEFT JOIN (SELECT AVG(rating) AS average_rating, salon_id FROM comments GROUP BY salon_id) a on a.salon_id = s.salon_id
GROUP BY s.salon_id HAVING distance < '\$radius' ORDER BY distance ASC
``````
0

Author Comment

ID: 38367199
That's perfect, thank you.
0

Author Comment

ID: 38367207
Sorry, a slight issue with the the results that are coming back.

If there are no comments associated with a salon, then the salon_id returned from the query is empty.
0

LVL 60

Expert Comment

ID: 38367307
Not sure what you mean? You should get all the results you usually did with the left hand query and null values for the average rating.

You have a SELECT * in your left hand query - change this to SELECT s.* otherwise it is going to give you the fields from the subquery (comments) and as there is a salon_id in that query as well it will override the one in the cms_salon table - giving the impression that the salon_id is empty. Query should look like this ...

``````SELECT s.*, 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, a.average_rating
FROM cms_salon s LEFT JOIN (SELECT AVG(rating) AS average_rating, salon_id FROM comments GROUP BY salon_id) a on a.salon_id = s.salon_id
GROUP BY s.salon_id HAVING distance < '\$radius' ORDER BY distance ASC
``````
0

Author Comment

ID: 38367327
Yes, that's it, thank you
0

LVL 60

Expert Comment

ID: 38367413
You are welcome
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
###### Suggested Courses
Course of the Month14 days, 4 hours left to enroll

#### 809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.