Severin - 23 days ago 4x
SQL Question

# SQL: Average from result set within a radius (Latitude / Longitude)

I'm trying to get the total average price within a radius by given latitude and longitude.

I've tried something like this:

``````SELECT avg(price) AS average,
( 6371 * acos( cos( radians(37.3541079) ) * cos( radians( ANY_VALUE(`latitude` )) ) * cos( radians( ANY_VALUE(`longitude`) ) - radians(-121.9552356) ) + sin( radians(37.3541079) ) * sin( radians( ANY_VALUE(`latitude`) ) ) ) ) AS distance
FROM `Rental`
HAVING distance <= 20
``````

But the result is empty.
The result should looks like this:

average = 185

You are using the `HAVING` in a way that MySQL extends it. But because you have `AVG()`, it places its normal role as well.

You are going to need a subquery or to move the distance measurement to the `WHERE` clause:

``````SELECT avg(price) as average
FROM (SELECT r.*,
( 6371 * acos( cos( radians(37.3541079) ) * cos( radians( ANY_VALUE(`latitude` )) ) * cos( radians( ANY_VALUE(`longitude`) ) - radians(-121.9552356) ) + sin( radians(37.3541079) ) * sin( radians( ANY_VALUE(`latitude`) ) ) ) ) AS distance
FROM `Rental` r
) r
WHERE distance <= 20;
``````

In other words, calculate the distance. Then use `WHERE` to filter the rows. And then aggregate for the price.