?
Solved

MYSQL - Generating an average rating

Posted on 2012-09-05
6
Medium Priority
?
574 Views
Last Modified: 2012-09-05
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

comments
- id
- text
- name
- rating
- salon_id
0
Comment
Question by:SheppardDigital
  • 3
  • 3
6 Comments
 
LVL 60

Accepted Solution

by:
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

Open in new window

0
 

Author Comment

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

Author Comment

by:SheppardDigital
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
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
LVL 60

Expert Comment

by:Julian Hansen
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

Open in new window

0
 

Author Comment

by:SheppardDigital
ID: 38367327
Yes, that's it, thank you
0
 
LVL 60

Expert Comment

by:Julian Hansen
ID: 38367413
You are welcome
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

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.

Join & Ask a Question