SELECT
sum(case when getAgeYearNum(p.birth,'2013-07-01') <5 THEN 1 ELSE '' END) AS '0-4',
sum(case when getAgeYearNum(p.birth,'2013-07-01') BETWEEN 5 and 9 THEN 1 ELSE '' END) AS '5-9',
sum(case when getAgeYearNum(p.birth,'2013-07-01') BETWEEN 10 and 14 THEN 1 ELSE '' END) AS '10-14',
sum(case when getAgeYearNum(p.birth,'2013-07-01') BETWEEN 15 and 19 THEN 1 ELSE '' END) AS '15-19',
sum(case when getAgeYearNum(p.birth,'2013-07-01') BETWEEN 20 and 24 THEN 1 ELSE '' END) AS '20-24',
sum(case when getAgeYearNum(p.birth,'2013-07-01') BETWEEN 25 and 29 THEN 1 ELSE '' END) AS '25-29',
sum(case when getAgeYearNum(p.birth,'2013-07-01') BETWEEN 30 and 34 THEN 1 ELSE '' END) AS '30-34',
sum(case when getAgeYearNum(p.birth,'2013-07-01') BETWEEN 35 and 39 THEN 1 ELSE '' END) AS '35-39',
sum(case when getAgeYearNum(p.birth,'2013-07-01') BETWEEN 40 and 44 THEN 1 ELSE '' END) AS '40-44',
sum(case when getAgeYearNum(p.birth,'2013-07-01') BETWEEN 45 and 49 THEN 1 ELSE '' END) AS '45-49',
sum(case when getAgeYearNum(p.birth,'2013-07-01') BETWEEN 50 and 54 THEN 1 ELSE '' END) AS '50-54',
sum(case when getAgeYearNum(p.birth,'2013-07-01') BETWEEN 55 and 59 THEN 1 ELSE '' END) AS '55-59',
sum(case when getAgeYearNum(p.birth,'2013-07-01') BETWEEN 60 and 64 THEN 1 ELSE '' END) AS '60-64',
sum(case when getAgeYearNum(p.birth,'2013-07-01') >= 65 THEN 1 ELSE '' END) AS '>=65',
COUNT(*) as 'รวม'
FROM person AS p
INNER JOIN house ON house.pcucode = p.pcucodeperson AND house.hcode = p.hcode
WHERE p.birth<'2013-07-01'
and SUBSTRING(house.villcode,7,2) <> '00'
AND p.typelive IN ('0','1','2')
and concat(p.pcucodeperson,p.pid) not in (SELECT concat(persondeath.pcucodeperson,persondeath.pid)
FROM persondeath
WHERE persondeath.deaddate < '2013-07-01' OR persondeath.deaddate IS NULL)
GROUP BY p.sex;