SELECT "min value 0.1%" AS "", -- PATIENT LIST AS COMMON DENOMINATOR COUNT(fin.patient) AS "Female Patients Aged [50,75]", -- COUNT ALL PATIENTS WITH MAMMOGRAMS PERFORMED IF( COUNT(fin.patient) > 0, ROUND( ( COUNT(fin.mam) * 100 ) / COUNT(fin.patient), 1 ), 0)AS "MAM Since {FROM} (%)", -- COUNT ALL ABNORMAL RESULTS IF( COUNT(fin.patient) > 0, IFNULL( ROUND( SUM(fin.abn = "abnormal") * 100 / COUNT(fin.patient), 1 ), 0 ), 0) AS "MAM Abn (%)" FROM ( SELECT d.demographic_no AS patient, MAM.id AS mam, MAM.val AS abn FROM demographic d -- GET ALL MAMOGRAM ENTRIES FROM PREVENTIONS LEFT JOIN ( SELECT p.demographic_no, p.id, pe.val FROM preventions p LEFT JOIN preventionsExt pe ON (pe.prevention_id = p.id AND pe.keyval LIKE "%result%") WHERE p.prevention_type LIKE "%MAM%" AND p.deleted = 0 AND DATE(p.prevention_date) >= DATE("{FROM}") ORDER BY pe.val ASC ) MAM ON (d.demographic_no = MAM.demographic_no) -- WHERE THE PATIENT'S AGE AT THE TIME OF THE COLONOSCOPY WHERE d.patient_status LIKE "%AC%" AND d.sex LIKE "%F%" 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;