Average value of entries up to 100km sql

Asked

Viewed 169 times

0

I’m making 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 registrations, including those that are at a greater distance. Without avg(valor) he brings only those who are within 100km distance without avg(value):

distancia
78.82683615218286
6.351835016010867

with avg(value)

avg(valor)       |distancia
76.66666666666667|78.82683615218286

the table is 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

1 answer

1


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

  • worked perfect, just to know, what is this Virutal that you put?

  • It is a name he gave to the table in memory generated by the most internal select.

  • some bds like mysql require the virtual table to have a name.

  • Hello, it’s working fine, but you know how I can bring who are the people who meet that criteria? ex: bring: avg(value):100 | id:1, avg(value):100 | id:2

Browser other questions tagged php sql query

You are not signed in. Login or sign up in order to post.