SET @d='2013-07-01'; #ประชากรกลางปี  ณ 1/7/56 แบ่งตามกลุ่ม 0-100 ปี (อายุ >=100 นับรวมกัน ) แยกชายหญิงและรายหมู่ (จำนวน 11 หมู่)
SET @num  = 0;

SELECT t.AGE,
t2.M01_M,t2.M01_F,
t2.M02_M,t2.M02_F,
t2.M03_M,t2.M03_F,
t2.M04_M,t2.M04_F,
t2.M05_M,t2.M05_F,
t2.M06_M,t2.M06_F,
t2.M07_M,t2.M07_F,
t2.M08_M,t2.M08_F,
t2.M09_M,t2.M09_F



 FROM

(SELECT @num := @num + 1 as AGE FROM
person LIMIT 100) t

LEFT JOIN

(SELECT 
IF(age_y>=100,100,age_y) AS AGE_YN,
sum(IF(mumoi='01' AND sex=1,1,0)) AS M01_M,
sum(IF(mumoi='01' AND sex=2,1,0)) AS M01_F,
sum(IF(mumoi='02' AND sex=1,1,0)) AS M02_M,
sum(IF(mumoi='02' AND sex=2,1,0)) AS M02_F,
sum(IF(mumoi='03' AND sex=1,1,0)) AS M03_M,
sum(IF(mumoi='03' AND sex=2,1,0)) AS M03_F,
sum(IF(mumoi='04' AND sex=1,1,0)) AS M04_M,
sum(IF(mumoi='04' AND sex=2,1,0)) AS M04_F,
sum(IF(mumoi='05' AND sex=1,1,0)) AS M05_M,
sum(IF(mumoi='05' AND sex=2,1,0)) AS M05_F,
sum(IF(mumoi='06' AND sex=1,1,0)) AS M06_M,
sum(IF(mumoi='06' AND sex=2,1,0)) AS M06_F,
sum(IF(mumoi='07' AND sex=1,1,0)) AS M07_M,
sum(IF(mumoi='07' AND sex=2,1,0)) AS M07_F,
sum(IF(mumoi='08' AND sex=1,1,0)) AS M08_M,
sum(IF(mumoi='08' AND sex=2,1,0)) AS M08_F,
sum(IF(mumoi='09' AND sex=1,1,0)) AS M09_M,
sum(IF(mumoi='09' AND sex=2,1,0)) AS M09_F
FROM
(SELECT p.pid,p.BIRTH,
getAgeYearNum(p.BIRTH,@d) as age_y,p.sex,
p.idcard,concat("'",h.hno) AS hnomoi,RIGHT(h.villcode,2) AS mumoi,p.typelive
FROM  person p 
LEFT JOIN house h ON h.pcucode=p.pcucodeperson AND h.hcode=p.hcode
WHERE p.typelive IN (1,3) AND RIGHT(h.villcode,2)<>'00' AND p.pid NOT IN (SELECT pid FROM persondeath)
ORDER BY mumoi,hnomoi) t1
WHERE age_y>=0
GROUP BY AGE_YN) t2

ON

t2.AGE_YN=t.AGE 