CODEWITHSUNDEEP
mysqlgroup-byhaving
Ward Bekker
Ward Bekker

Reputation: 6366

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

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

Upvotes: 1

Views: 1827

Answers (1)

Ken White
Ken White

Reputation: 125728

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

Upvotes: 1

Related Questions