MySQL Average Query only returning one row

Question

I'm developing a rating system as part of a web application. I'm trying to run a query which returns the average for each entry in the data set. Below is the query I've started with:

SELECT hotel_id, 
hotel_logo, 
hotel_name, 
hotel_website, 
hotel_facebook, 
hotel_rating, 
hotel_verified,
hotel_location,
hotel_founded_in
FROM hotels

This works as expected and returns all the hotels & specified data fields.

To try and get the average of each set of reviews under a hotel I've used to following:

SELECT hotels.hotel_id, 
AVG(review_total_rating),
hotel_logo, 
hotel_name, 
hotel_website, 
hotel_facebook, 
hotel_rating, 
hotel_verified,
hotel_location,
hotel_founded_in
FROM hotels
INNER JOIN reviews
ON hotels.hotel_id = reviews.hotel_id

This query is returning the correct values but ONLY for a single row, I am also getting a MySQL warning saying there is no unique identifier.

How can I adjust the above query to bring back the specified data for every field in the table?

Any help appreciated.

you have to do GROUP BY hotel_id,hotel_logo,hotel_name,hotel_website,hotel_rating... — Igmer Rodriguez
– Igmer Rodriguez, Commented Jun 3, 2019 at 19:27
To point out what others didn't, if you were getting the correct average, it was only coincidentally. Without the GROUP BY, you were getting the average of all joined records.... and an effectively random selection of values for the non-aggregated, non-grouped fields. — Uueerdo
– Uueerdo, Commented Jun 3, 2019 at 20:07

Sal · Accepted Answer · 2019-06-03 19:35:22Z

1

Just add: GROUP BY hotels.hotel_id assuming it is the PK.

answered Jun 3, 2019 at 19:35

Sal

1,3171 gold badge8 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

bbowesbo Over a year ago

I have just done this, and it works mostly. The only problem is that if an entry has no value for AVG(review_total_rating) in the review table the whole entry is ignored. Is there a way for me to return the rest of the data for the row rather than ignoring the whole entry if the total review field is empty?

Sal Over a year ago

Yes, just use LEFT JOIN instead of INNER JOIN.

Collectives™ on Stack Overflow

MySQL Average Query only returning one row

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related