How to sort an SQL table according to an average value in another table

Image of table relationships and fields

I am having great difficulty making a query where I want to sort my query according to an average in another table. I have a tblHotels table with a HotelID, HotelName, PricePerNight, LeftCoordinate and TopCoordinate and a tblRatings table with HotelID and HotelRating (Ranked from 1 to 10 from various users/multiple ratings).

I want my query to show all the hotels where the PricePerNight (from tblHotels) is within a given range (BETWEEN lowervalue AND uppervalue) to show and then order it by their ratings in tblRatings.

SELECT tblRatings.HotelID
    , tblHotels.HotelName
    , LeftCoordinate
    , TopCoordinate
    , AVG(HotelRating) AS Rating 
FROM tblRatings, tblHotels 
WHERE ( SELECT HotelID 
        FROM tblHotels 
        WHERE PricePerNight BETWEEN LowerValue AND UpperValue) = 
        tblRatings.HotelID 
GROUP BY tblRatings.HotelID 
ORDER BY AVG(HotelRating) DESC

SELECT tblRatings.HotelID
    , HotelName
    , LeftCoordinate
    , TopCoordinate
    , AVG(HotelRating) AS Rating 
FROM tblHotels, tblRatings 
WHERE (PricePerNight BETWEEN LowerValue AND UpperValue) AND (tblHotels.HotelID = tblRatings.HotelID) 
GROUP BY tblRatings.HotelID 
ORDER BY AVG(HotelRating) DESC

I have tried a subquery and a related table query but both give me the error “HotelName is not part of an aggregate function”

I have looked online to try and find a solution but to no success 🙁

Answer

basically you need to group by more things…

SELECT tblRatings.HotelID
    , HotelName
    , LeftCoordinate
    , TopCoordinate
    , AVG(HotelRating) AS Rating 
FROM tblHotels, tblRatings 
WHERE (PricePerNight BETWEEN LowerValue AND UpperValue) AND (tblHotels.HotelID = tblRatings.HotelID) 
GROUP BY tblRatings.HotelID, HotelName, LeftCoordinate, TopCoordinate
ORDER BY AVG(HotelRating) DESC