MySQL COUNT (*) returns an empty result set

Question

I am currently doing large circle distance calculations in a table in MySQL. The table is 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() return result, MySQL returns an empty result set.

For example, the following query returns 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 

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

Can anyone guess why this is happening, and how can I 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 correct outputs:

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

Answer 1

checked this and it seems to return what you want. let me know if this is not correct

 SELECT SUM(1) 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 GROUP BY distance HAVING distance < 5 ORDER BY distance 

FIDDLE DEMO

The main problem is the lack of a group and an AGGREGATE function, it groups all the lines into one ... you need to say to separate them. so I said at a distance, since you get a list of distances in lat and long.

EDIT:

if you want a single row to be returned showing the general locations at distance 5 and the total distance, you can do something like this.

 SELECT SUM(1) as total, SUM(distance) as total_distance, MAX(distance) as furthest_location, MIN(distance) as closest_location FROM ( SELECT ( 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 ) t 

SECOND DEMO

Answer 2

To count the lines 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 

Once you have selected the desired lines, you can get the score with this single request:

 SELECT FOUND_ROWS(); 

Answer 3

I believe that I understand why using HAVING does not return any result, because HAVING will filter the string after grouping. Since you are grouping the whole table, only one row will be returned. Then it will be filtered by HAVING .

Here is a clearer image,

 SELECT COUNT(*) AS total, (....) AS distance FROM wv_venue_locations 

This query will return a single row. Let them say that the line

 total, distance 100, 6.432 

Then, applying HAVING distance < 5 . There is no line matching this condition.

---

I do not know if this wants what the OP wants

 SELECT COUNT(*), MIN(distance) FROM ( SELECT ( 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 ) AS t WHERE distance < 5 

Answer 4

Your distance < 5 should be in the WHERE state, and not in HAVING . HAVING will be used if you want to show results when the result of COUNT() meets certain criteria.