Count query returning unexpected results

I have this query:

SELECT
    count(*) as count ,
    ( 3959 * acos(
        cos( radians( 37.774929 ) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians( -122.419418 ) )
        + sin( radians( 37.774929 ) ) * sin( radians( lat ) )
    ) ) AS distance
FROM users
HAVING distance < 150

I thought it was going to give me the count of users who are in the radius of 150 miles. But instead it gave me a total number of users. And if lat/lng were different, it would give m开发者_运维技巧e zero number of users when there were some users there.

Any ideas how to change this query in order to get the number of users within the 150mi radius?

Thanks!

You want to use a WHERE clause

select count(*) as count from users WHERE ( 3959 * acos( cos( radians( 37.774929 ) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians( -122.419418 ) ) + sin( radians( 37.774929 ) ) * sin( radians( lat ) ) ) ) < 150

To expand on Dirk's answer...

• The WHERE clause is applied to the records after they've all be joined up.
• The HAVING clause is applied after any aggregation is complete.

Also, as in Dirk's answer, you don't need to have the calculation in the SELECT to be able to use it elsewhere.

So, Dirk's answer calculates the Distance for each individual ROW (after joins, before aggregation), then discards any with a distance of 150 or more. It only THEN aggregates and counts everything up.