Severin Severin - 2 months ago 11
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


Thanks for your help.

Answer

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.