mysql – Strange COUNT(*) issue

This doesn’t look to be a particularly well formed query, but the crux is the HAVING clause only makes sense within the scope of a GROUP BY statement.

So you can do this two ways:

1. Calculate the distance within a subquery, then take a count from that using a WHERE clause to limit to cases where the distance is less than 10
2. Use a CASE statement within a SUM to count only if the distance is less than 10.

Since you are using MySQL 5.7, you should be able to take advantage of the distance functions. I’m not sure what units your desired result is using, but ST_Distance_Sphere will return meters:

SELECT
  COUNT(*) AS total
FROM
  (
    SELECT
      J.Id
     ,ST_Distance_Sphere(POINT(J.Lat,J.Lng),POINT(UserM.userLat,UserM.userLng)) AS distance
    FROM 
      members UserM
    CROSS JOIN
      doctors J
    WHERE
      UserM.id = 100
  ) x
WHERE
  distance < /* however many meters radius required */

Using your original calculation:

SELECT
  COUNT(*) AS total
FROM
  (
    SELECT
      J.Id
     ,3959 * acos
        (
          cos(radians(J.lat)) * cos(radians(UserM.userLat)) * cos(radians(UserM.userLng) - radians(j.lng))
            + sin(radians(J.lat)) * sin(radians(UserM.userLat))
        ) AS distance
    FROM 
      members UserM
    CROSS JOIN
      doctors J
    WHERE
      UserM.id = 100
  ) x
WHERE
  distance < 10