Need help with complicated query

[rhughessa]

So here's the query I'm trying which is returning a "error in your syntax" error

 

SELECT COUNT(restaurant_id) AS number, category, lat, lon, ( 3959 * acos( cos( radians(41.044128) ) * cos( radians( lat ) ) * cos( radians( lon ) - radians(-81.524117) ) + sin( radians(41.044128) ) * sin( radians( lat ) ) ) ) AS distance FROM restaurants HAVING distance < 25 GROUP BY category ORDER BY category

 

This is an attempt at combining the following 2 queries that both work independently.

 

1.

SELECT COUNT(restaurant_id) AS number FROM restaurants GROUP BY category ORDER BY category

 

 

2.

SELECT lat, lon, ( 3959 * acos( cos( radians(41.044128) ) * cos( radians( lat ) ) * cos( radians( lon ) - radians(-81.524117) ) + sin( radians(41.044128) ) * sin( radians( lat ) ) ) ) AS distance FROM restaurants HAVING distance < 25

 

 

Thanks in advance,

 

Robert

[blacknight]

what is the full syntax error?

[ngreenwood6]

Well that seems to be a cool query and all but I see one flaw with it. When you have a COUNT() in the query it only returns one row. So you are not going to get more than one result with that query.

[kickstart]

Hi

 

The problem is that you have the HAVING clause in the wrong place. If should be after the GROUP BY.

 

However you shouldn't have non aggregate columns in the SELECT clause which are not in the GROUP BY clause. MySQL is pretty tolerant of this, but some flavours of SQL will reject it out of hand.

 

All the best

 

Keith