pass_fail

gistfile1.txt

SELECT u.id, u.name, COUNT(CASE WHEN `assessment_results`.passed = 1 THEN 1 ELSE NULL END) / COUNT(*) as pass_rate, COUNT(*) as total, COUNT(CASE WHEN `assessment_results`.passed = 1 THEN 1 ELSE NULL END) FROM users u 
LEFT JOIN assessment_results ON assessment_results.user_id = u.id
WHERE u.certification_id = 3
AND (u.certification_status = 'green' OR u.certification_status = 'yellow')
AND assessment_results.submitted = true
group by u.id
ORDER BY pass_rate DESC a