Grouping and counting of records in Haversine Formula SQL Query

Question

I am working on a vehicle trading website. I have table named vehicle with around 20 fields including make, model etc of the vehicle. I am writing a search query to retrieve all the cars in the system group by make along with their counts by make.

So my objective is to get all of vehicles in the system grouped by their make ALONG with their counts of the make but the counts part does not work correctly. It returns total number of cars by make ignoring my distance calculation criteria.

I am executing following following SQL:

SELECT * FROM (SELECT *,ROUND(((ACOS(SIN(51.4811109 * PI() / 180) * SIN(latitude * PI() / 180) + COS(51.4811109 * PI() / 180) * COS(latitude * PI() / 180) * COS((-0.433641 - longitude) * PI() / 180)) * 180 / PI()) * 60 * 1.1515),2) AS distance, count(make) AS carcount FROM `vehicle` `t` WHERE (status='Active')) as v GROUP BY make HAVING distance<=10

It is returning everything correct except the carcount for which it returns 325 BMW cars in the system(total BMW cars in the system) instead of 12 BMW cars(only 10 cars exist in 10 miles distance).

Can anyone see what I am doing wrong? Thanks for your help.

You need filter first , group later. – Tim3880 May 31 '15 at 18:29 — Tim3880, May 31 '15 at 18:29

BICube · Accepted Answer · 2015-05-31T18:33:24.947

You need to have the condition in where clause not having

SELECT *, count(*)
FROM 
(SELECT *,ROUND(((ACOS(SIN(51.4811109 * PI() / 180) * 
              SIN(latitude * PI() / 180) + COS(51.4811109 * 
              PI() / 180) *
              COS(latitude * PI() / 180) * 
              COS((-0.433641 - longitude) * PI() / 180)) *
              180 / PI()) * 60 * 1.1515),2) AS distance
FROM `vehicle` `t` 
WHERE (status='Active')) as v
WHERE distance<=10 
GROUP BY make

Tim3880 · Answer 2 · 2015-05-31T18:45:28.287

2

Try this

SELECT make, count(*) 
FROM vehicle
Where 
ROUND(((ACOS(SIN(51.4811109 * PI() / 180) * SIN(latitude * PI() / 180) + COS(51.4811109 * PI() / 180) * COS(latitude * PI() / 180) * COS((-0.433641 - longitude) * PI() / 180)) * 180 / PI()) * 60 * 1.1515),2) <= 10
    And status='Active
  GROUP BY make

edited May 31 '15 at 18:45

answered May 31 '15 at 18:34

Tim3880

2,563
1
11
14

I have tried both your's and Ala's SQL. Yours is giving incorrect count whereas Ala's is giving right counts. Thanks a lot for your answer by the way. – Hammad May 31 '15 at 18:42
Maybe the difference of "<10" and "<=10"? – Tim3880 May 31 '15 at 18:45

Grouping and counting of records in Haversine Formula SQL Query

2 Answers2