SELECT "min value 0.1%" AS "", -- PATIENT LIST AS COMMON DENOMINATOR COUNT(fin.patient) AS "Patients Aged [50,75]", -- COUNT ALL PATIENTS WITH COLONOSCOPYS PERFORMED IF( COUNT(fin.patient) > 0, ROUND( ( COUNT(fin.col) * 100 ) / COUNT(fin.patient), 1 ), 0) AS "COL Since {FROM} (%)", -- COUNT ALL ABNORMAL RESULTS IF( COUNT(fin.patient) > 0, IFNULL( ROUND( SUM(fin.abn LIKE "abnormal%") * 100 / COUNT(fin.patient), 1 ), 0), 0) AS "COL Abn (%)" FROM ( SELECT d.demographic_no AS patient, prev.id AS col, prev.val AS abn FROM demographic d -- LOOK FOR ENTRIES IN THE PREVENTIONS TABLE LEFT JOIN ( SELECT p.demographic_no, p.id, pe.val FROM preventions p INNER JOIN preventionsExt pe ON (pe.prevention_id = p.id AND pe.keyval LIKE "%result%") WHERE p.prevention_type LIKE "%COLONOSCOPY%" AND p.deleted = 0 AND DATE(p.prevention_date) > DATE("{FROM}") ORDER BY pe.val ASC ) prev ON (d.demographic_no = prev.demographic_no ) -- WHERE THE PATIENT'S AGE AT THE TIME OF THE COLONOSCOPY WHERE d.patient_status LIKE "%AC%" AND 50 <= ROUND( ABS( DATEDIFF( DATE( CONCAT(d.year_of_birth,"-",d.month_of_birth,"-",d.date_of_birth) ), DATE("{FROM}") ) / 365.25 ) ) AND 75 >= ROUND( ABS( DATEDIFF( DATE( CONCAT(d.year_of_birth,"-",d.month_of_birth,"-",d.date_of_birth) ), DATE("{FROM}") ) / 365.25 ) ) GROUP BY d.demographic_no HAVING COUNT(d.demographic_no) > -1 ) fin;