SELECT vacancy_added_date, job_title, no_of_positions, no_of_applicants, SUM(no_of_shortlisted) AS no_of_shortlisted, SUM(no_of_hired) AS no_of_hired
FROM
(
  SELECT
  v.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 AND jcv.deleted_at IS NULL
  ) AS `no_of_applicants`,
  MAX(IF(ve.type_id = 2 OR ve.type_id = 3 OR ve.type_id = 4 OR ve.type_id = 5 OR ve.type_id = 6, 1, 0)) AS `no_of_shortlisted`,
  MAX(IF(type_id = 14, 1, 0)) AS `no_of_scheduled`,
  MAX(IF(type_id = 15, 1, 0)) AS `no_of_passed`,
  MAX(IF(type_id = 16, 1, 0)) AS `no_of_failed`,
  MAX(IF(type_id = 7, 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_vacancy` AS jcv
    ON jcv.vacancy_id = v.id AND jcv.deleted_at IS NULL
  INNER JOIN `ohrm_candidate_vacancy_event` AS cve
    ON cve.vacancy_candidate_id = jcv.id
  INNER JOIN `ohrm_vacancy_event` AS ve
    ON ve.id = cve.event_id
  INNER JOIN `ohrm_candidate_event_state` AS ces
    ON ces.candidate_vacancy_event_id = cve.id
  WHERE ces.active = 1
  GROUP BY jcv.candidate_id
  ) AS r
WHERE whereCondition1
GROUP BY r.id
ORDER BY r.vacancy_added_date DESC
;