SELECT selectCondition
FROM
(
  SELECT
  v.id,
  v.hiring_manager_id,
  v.job_title_code,
  DATE(v.defined_time) AS `vacancy_added_date`,
  jt.job_title AS `job_title`,
  v.no_of_positions AS `no_of_positions`,
  (
    SELECT COUNT(*) FROM ohrm_job_candidate_vacancy AS jcv
    WHERE jcv.vacancy_id = v.id
  ) AS `no_of_applicants`,
  SUM(IF(jch.action = 2, 1, 0)) AS `no_of_shortlisted`,
  SUM(IF(action = 4, 1, 0)) AS `no_of_scheduled`,
  SUM(IF(action = 5, 1, 0)) AS `no_of_passed`,
  SUM(IF(action = 6, 1, 0)) AS `no_of_failed`,
  SUM(IF(action = 9, 1, 0)) AS `no_of_hired`
  FROM `ohrm_job_vacancy` v
  INNER JOIN `ohrm_job_title` AS jt
    ON v.job_title_code = jt.id
  INNER JOIN `ohrm_job_candidate_history` AS jch
    ON jch.vacancy_id = v.id
  GROUP BY jch.candidate_id
  ) AS r
WHERE whereCondition1
GROUP BY r.id
ORDER BY r.vacancy_added_date DESC
;