SELECT job.id, DISTINCT company_name, city, job.title, hourly_rate_amount, created_at,
COUNT(work_intent.id), number_of_contractors,
(COUNT(work_intent.id)/number_of_contractors) AS aantal_reacties,
(3959 * acos(cos(radians(51.54348510000000)) * cos( radians(address.latitude))
* cos(radians(longitude) - radians(4.94570490000000)) + sin(radians(51.54348510000000)) * sin(radians(latitude)))) AS afstand_klus
FROM job
INNER JOIN client ON job.client_id = client.id
INNER JOIN address ON job.address_id = address.id
LEFT JOIN work_intent ON job.id = work_intent.job_id
INNER JOIN job_title ON job.job_title_id = job_title.id
WHERE job_title.id = ANY
(SELECT job_title.id FROM job_title WHERE job.job_title_id = '28'
OR job.job_title_id = '30'
OR job.job_title_id = '31'
OR job.job_title_id = '32'
OR job.job_title_id = '35'
OR job.job_title_id = '38'
OR job.job_title_id = '39'
OR job.job_title_id = '40'
OR job.job_title_id = '41'
OR job.job_title_id = '42'
OR job.job_title_id = '46'
OR job.job_title_id = '49'
OR job.job_title_id = '50'
OR job.job_title_id = '52'
OR job.job_title_id = '53'
OR job.job_title_id = '55'
OR job.job_title_id = '59'
OR job.job_title_id = '60'
OR job.job_title_id = '61'
OR job.job_title_id = '62'
OR job.job_title_id = '66'
OR job.job_title_id = '67'
OR job.job_title_id = '68'
OR job.job_title_id = '70'
)
AND job.STATUS = 'open'
AND CONVERT(job.starts_at, DATE) = '2019-09-25'
AND hourly_rate_amount > 1300
GROUP BY job.id
HAVING afstand_klus < 200
ORDER BY aantal_reacties, afstand_klus
LIMIT 100