SELECT house.villcode,
SUM(CASE WHEN personstudent.classeducate = '4' THEN 1 ELSE '' END)AS 'ป1',
SUM(CASE WHEN personstudent.classeducate = '9' THEN 1 ELSE '' END)AS 'ป6'
FROM person INNER JOIN personstudent ON person.pid = personstudent.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
union select 'รวม'as villcode,
SUM(CASE WHEN personstudent.classeducate = '4' THEN 1 ELSE '' END)AS 'ป1',
SUM(CASE WHEN personstudent.classeducate = '9' THEN 1 ELSE '' END)AS 'ป6'
FROM person INNER JOIN personstudent ON person.pid = personstudent.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