COUNT's gone weird

[rarebit]

Hi,

 

From a number of search queries a statement is made and used as a COUNT, however for some reason today it's gone wrong, but I can't see why...

 

SELECT latitude, longitude, ( 60 * 1.1515 * DEGREES(ACOS(SIN(RADIANS(53.3540)) * SIN(RADIANS(latitude)) + COS(RADIANS(53.3540)) * COS(RADIANS(latitude)) * COS(RADIANS(-1.4890-longitude)))) ) as dist, 
COUNT(*) as num 
FROM basic 
LEFT JOIN pcodes ON pcodes.postcode = basic.postcode 
GROUP BY basic.id 
HAVING ( dist <= 200 )

 

num = 1

 

but it shouldn't!

 

Any ideas? ? ?

 

p.s. I've also tried selecting 'basic.id', to no avail...

[fenway]

Why the group by ? Aren't the ID's unique?

[rarebit]

Yes the id's are unique, but I don't really want to return the full list (even if only id's) and then count them because it's wasteful (there should be at least 2k+ entries) on the mysql network and probably quicker to do it with mysql.

 

Here's how i've altered it for the counting statement (there is also a more complex selecting statement (which still works!) ):

SELECT latitude, longitude, basic.id, COUNT(*) as num 
FROM basic 
LEFT JOIN pcodes ON pcodes.postcode = basic.postcode 
WHERE ( ( 60 * 1.1515 * DEGREES(ACOS(SIN(RADIANS(53.3540)) * SIN(RADIANS(latitude)) + COS(RADIANS(53.3540)) * COS(RADIANS(latitude)) * COS(RADIANS(-1.4890-longitude)))) ) <= 200 ) 
GROUP BY basic.id

[fenway]

I'm still quite confused... how can you aggregate on ID *and* include some values for "each" row?