COUNT(*) and Having

The following query gets me a column of distances. But what i need is only the count of results with matching distances, not the distances themselves. A Subselect cannot be used.

SELECT 
( 6368 * SQRT(2*(1-cos(RADIANS(loc_lat)) * cos(0.899945742869) * (sin(RADIANS(`loc_lon`)) * sin(0.14286767838) + cos(RAD开发者_运维问答IANS(`loc_lon`)) * cos(0.14286767838)) - sin(RADIANS(loc_lat)) * sin(0.899945742869))) ) AS Distance

FROM ...

WHERE ...

HAVING Distance > 0 AND Distance <= 25 

You just need to move the distance calculation to the where clause:

SELECT COUNT(*) FROM ...

WHERE ( 6368 * SQRT(2*(1-...) BETWEEN 0 AND 25

If you don't need the distances, only the count, maybe this will work:

SELECT Count(*)
FROM ...
WHERE ... AND
  (6368 * SQRT(2*(1-cos(RADIANS(loc_lat)) * cos(0.899945742869) *
  (sin(RADIANS(`loc_lon`)) * sin(0.14286767838) + cos(RADIANS(`loc_lon`)) *
   cos(0.14286767838)) - sin(RADIANS(loc_lat)) * sin(0.899945742869)))
  ) BETWEEN 0 AND 25

This will give the totalResults, and you can discard the other column.

 SELECT COUNT(*) totalResults, 
    ( 6368 * SQRT(2*(1-cos(RADIANS(loc_lat)) * cos(0.899945742869) * (sin(RADIANS(`loc_lon`)) * sin(0.14286767838) + cos(RADIANS(`loc_lon`)) * cos(0.14286767838)) - sin(RADIANS(loc_lat)) * sin(0.899945742869))) ) AS Distance

    FROM ...

    WHERE ...

    HAVING Distance > 0 AND Distance <= 25