How to get away with non-grouping field in HAVING clause

Question

When executing in *ONLY_FULL_GROUP_BY* mode, I get the error "non-grouping field 'distance' is used in HAVING clause" when executing the following query. The query counts the amount of hotels that are within 15 km distance of a certain latitude & longitude. Is there a way to rewrite this query so I don't get the error anymore in *ONLY_FULL_GROUP_BY* mode?

SELECT count(id) as total, (foo * 100) AS 'distance'
FROM `hotels` 
WHERE `lng` between 4.56 and 5.08 and `lat` between 52.22 and 52.65 
HAVING `distance` < 15

Answer 1

Change the HAVING to an additional WHERE clause:

WHERE `lng` BETWEEN 4.56 AND 5.08 AND `lat` BETWEEN 52.22 AND 52.65 
AND `distance` < 15

I'm not sure if MySQL supports using the aliased column name in the WHERE clause (distance in this case). If not, just change it to:

AND (foo * 100) < 15