Severin - 1 year ago 55

SQL Question

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 Source

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.