MySQL COUNT(*) returns empty result set

Question

I am currently performing great-circle distance calculations on a table in MySQL. The table looks as follows:

+----------+---------+---------+
| venue_id | lat     | lng     |
+----------+---------+---------+
| 1        | 1.23456 | 2.34567 |
+----------+---------+---------+
| 2        | 1.23456 | 2.34567 |
+----------+---------+---------+
| 3        | 1.23456 | 2.34567 |
+----------+---------+---------+

However, when I try to create a COUNT() of the results returned, MySQL returns an empty result set.

For example, the following query produces no results:

SELECT COUNT(*) AS total, 
       ( 3959 * Acos(Cos(Radians('52.97682200')) * Cos(Radians(lat)) * Cos( 
                                   Radians(lng) - Radians(-0.02210000)) + 
                              Sin(Radians(52.97682200)) * Sin(Radians(lat))) ) 
       AS distance 
FROM   wv_venue_locations 
HAVING distance < 5
ORDER  BY distance

Yet, running the same query without the COUNT(*) AS total field results in the expected 6 results.

Can anyone suggest why this happens, and how I might fix it?

As a post-script to the above, the following works fine:

SELECT Count(*) 
       AS 
       total, 
       ( 3959 * Acos(Cos(Radians(53.18335000)) * Cos(Radians(lat)) * Cos( 
                                   Radians(lng) - Radians(-0.29600000)) + 
                              Sin(Radians(53.18335000)) * Sin(Radians(lat))) ) 
       AS 
       distance 
FROM   wv_venue_locations 
WHERE  lat >= 52.64017900
       AND lat <= 53.72650900
       AND lng >= -0.94998000
       AND lng <= 0.35798000

The above correctly outputs:

+-------+-------------------+
| total | distance          |
+-------+-------------------+
| 224   | 27.93840157954865 |
+-------+-------------------+

Answer 1

To count rows you can do this

SELECT SQL_CALC_FOUND_ROWS 
       ( 3959 * Acos(Cos(Radians('52.97682200')) * Cos(Radians(lat)) * Cos( 
                                   Radians(lng) - Radians(-0.02210000)) + 
                              Sin(Radians(52.97682200)) * Sin(Radians(lat))) ) 
       AS distance 
FROM   wv_venue_locations 
HAVING distance < 5
ORDER  BY distance

After you have selected needed rows, you can get the count with this single query:

SELECT FOUND_ROWS();