Calculate average of the value of registrations up to 100km sql

Question

Calculate average of the value of registrations up to 100km sql

Navigation

#1 by (1 votes)

1

I'm doing a query that brings the average values, as long as the distance is up to 90km:

SELECT avg(valor),(6371 * acos(
 cos( radians(-23.2632227) )
 * cos( radians( lat ) )
 * cos( radians( lng ) - radians(-45.9414303) )
 + sin( radians(-23.2632227) )
 * sin( radians( lat ) ) 
 )
) AS distancia
FROM fcs_prestadores
HAVING distancia < 90

The problem is that when I put avg (value) it calculates the average of all the registers, including those that are at a greater distance. Without the avg (value) it only brings the ones that are within the distance of 100km without avg (value):

distancia
78.82683615218286
6.351835016010867

with avg (value)

avg(valor)       |distancia
76.66666666666667|78.82683615218286

The table looks like this:

id | nome|valor|lat        |lng
1  |joana|100  |-23.542746 |-46.651302
2  |maria|120  |-23.241726 |-45.883827
3  |lucia|40   |-22.8117593|-45.1786567

Thank you!

php sql query

asked by anonymous 25.05.2017 / 16:59

1 answer

List the last message of each user Customers who bought in one year but not in another [closed]

score 1 · Accepted Answer

try:

select avg(valor)
from
(
SELECT valor,(6371 * acos(
 cos( radians(-23.2632227) )
 * cos( radians( lat ) )
 * cos( radians( lng ) - radians(-45.9414303) )
 + sin( radians(-23.2632227) )
 * sin( radians( lat ) ) 
 )
) AS distancia
FROM fcs_prestadores
) virtual 
where  distancia < 90