CODEWITHSUNDEEP
sqlcount
Joe
Joe

Reputation: 1

SQL count on a created column

The following query calculates the distance of users based on geocoordinates. It returns 4 results for people within 1 miles. How can I count the result and return it?

select  ((ACOS(SIN(40.7643030* PI() / 180) * 
                         SIN(users.lat * PI() / 180) + 
                         COS(40.7643030* PI() / 180) * 
                         COS(users.lat * PI() / 180) * 
                         COS((-73.9730040- users.lon) * PI() / 180)) * 
                         180 / PI()) * 60 * 1.1515)         
                         AS `distance` FROM users HAVING distance < 1

I tried to add 'COUNT(*) AS count' after distance but it returns no results.

Thanks for your help.

Upvotes: 0

Views: 312

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270191

You can use a subquery:

select count(*)
from (select  ((ACOS(SIN(40.7643030* PI() / 180) * 
                         SIN(users.lat * PI() / 180) + 
                         COS(40.7643030* PI() / 180) * 
                         COS(users.lat * PI() / 180) * 
                         COS((-73.9730040- users.lon) * PI() / 180)) * 
                         180 / PI()) * 60 * 1.1515)         
                         AS `distance`
      FROM users
      HAVING distance < 1
     ) t;

Upvotes: 1

Related Questions