I am querying the DB to get all venues within a certain X Radius of a given latitude & longitude.
Everything works good until I insert a count & group by, causing the Having clause to no longer filter out records that are
SELECT
locations.id AS 'locations_id_c',
locations.city AS 'locations_city_c',
locations.province AS 'locations_state_c',
locations.latitude AS 'latitude_c',
locations.longitude AS 'longitude_c',
venues.id AS 'venues_id_c',
venues.name AS 'venues_name_c',
venues.venue_rank,
venues.median_fb_likes,
count(gigs.show_time),
count(band_profiles.name) AS 'Related Band',
#san fran lat & long = 37.7833° N, 122.4167° W
# ( 3959 * acos( cos( radians(37.7833) ) * cos( radians( locations.latitude) ) * cos( radians( locations.longitude ) - radians(-122.4167) ) + sin( radians(37.7833) ) * sin(radians(locations.latitude)) ) ) AS 'distance'
3956 * 2 * ASIN(SQRT( POWER(SIN((37.7833 - abs(locations.latitude)) * pi()/180/2),2) +COS(37.7833 * pi()/180 )*COS(abs(locations.latitude)*pi()/180) *POWER(SIN((locations.longitude + 122.4167)*pi()/180/2),2))) as 'distance'
FROM locations
INNER JOIN venues
ON locations.locatable_id = venues.id
AND venues.venue_rank >= 0
LEFT JOIN gigs
ON gigs.venue_id = venues.id
and gigs.show_time > NOW()
LEFT JOIN band_profiles
ON gigs.account_id = band_profiles.account_id
and band_profiles.name IS NOT NULL
WHERE
locations.locatable_type = 'Venue'
group by venues.id
HAVING distance < 50
ORDER BY venues.venue_rank DESC, distance DESC;
