SELECT house.villcode ,
SUM(CASE WHEN personstudent.classeducate = '4' and visitepi.vaccinecode = 'MMRS' THEN 1 ELSE '' END)as 'MMRS',
SUM(CASE WHEN personstudent.classeducate = '9' and visitepi.vaccinecode = 'DTS4' THEN 1 ELSE '' END)as 'DTS4'
FROM person INNER JOIN personstudent ON person.pid = personstudent.pid
INNER JOIN visitepi ON person.pcucodeperson = visitepi.pcucodeperson AND person.pid = visitepi.pid
INNER JOIN house ON person.pcucodeperson = house.pcucode
AND person.hcode = house.hcode
INNER JOIN village ON house.pcucode = village.pcucode
AND village.villcode = house.villcode
group by house.villcode

