mysql - Count locations withing given distance

Question

I have the following query, that selects places withing given radius:

SELECT *
FROM (
  SELECT 
    group, 
    name,  
    111.1111 *
    DEGREES(ACOS(LEAST(1.0, COS(RADIANS(".$lat."))
       * COS(RADIANS(t1.lat))
       * COS(RADIANS(".$lon.") - RADIANS(t1.lon))
       + SIN(RADIANS(".$lat."))
       * SIN(RADIANS(t1.lat))))) AS distance
  FROM t1
) AS grp
WHERE distance < 10

Is there a way to count how many locations in each group withing this query?

question from:https://stackoverflow.com/questions/65836041/count-locations-withing-given-distance

Answer 1

In MySQL you can use HAVING to reduce the number of rows.

SELECT 
    `group`, COUNT(*) NumOfNames
FROM
    (SELECT 
        `group`,
            name,
            ST_DISTANCE_SPHERE(POINT('.$lon.', '.$lat.'), POINT(t1.lon, t1.lat)) / 1000 AS distance
    FROM
        t1
    HAVING distance < 10) t2
GROUP BY `group`