All geek questions in one place

COUNT (*) and Availability

The following query gets me a distance column. But I only need to count the results with the corresponding distances, not the distances themselves. The subrecord cannot be used.

SELECT ( 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
+4
source share
3 answers

If you don't need distances, just 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
+2
source

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

 SELECT COUNT(*) FROM ... WHERE ( 6368 * SQRT(2*(1-...) BETWEEN 0 AND 25
+3
source

This will give the final results, and you can drop another 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
+1
source

Source: https://habr.com/ru/post/1369501/

More articles:


All Articles