Grouping and Counting Records in Haversine Formula SQL Query
I work for a trading website. I have a table with a vehicle name with about 20 fields including make, model, etc. Car. I am writing a search query to get all the cars in the system group done along with their counts.
Thus, my goal is to collect all the systems in the system, grouped by their ALONG command, with their counts, but some of the counters are not working correctly. It returns the total number of cars, ignoring my criteria for calculating the distance.
I am executing the 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 returns all correct values, except carcount
for which it returns 325 BMW cars in the system (total BMW cars in the system) instead of 12 BMW cars (total 10 cars exist 10 miles away).
Can anyone see what I am doing wrong? Thanks for your help.
source to share
You must have a condition where the offer does not have
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
source to share
try it
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
source to share