SELECT
village.villno AS 'หมู่ที่',
COUNT(DISTINCT person.pid) AS 'จำนวนเป้าหมายประชากร อายุ 15-19 ปี(คน)',
SUM(CASE WHEN personbehavior.wisky BETWEEN '1' AND '6' THEN '1' ELSE NULL END) AS 'ดื่ม',
round(SUM(CASE WHEN personbehavior.wisky BETWEEN '1' AND '6' THEN '1' ELSE NULL END) * 100 / COUNT(DISTINCT person.pid),2) AS 'ร้อยละ',
SUM(CASE WHEN personbehavior.wisky = '0' THEN '1' ELSE NULL END) AS 'ไม่ดื่ม',
round(SUM(CASE WHEN personbehavior.wisky = '0' THEN '1' ELSE NULL END) * 100 / COUNT(DISTINCT person.pid),2) AS 'ร้อยละ'
FROM
village
INNER JOIN house ON house.pcucode = village.pcucode AND house.villcode = village.villcode
INNER JOIN person ON person.pcucodeperson = house.pcucode AND person.hcode = house.hcode
LEFT JOIN personbehavior ON person.pcucodeperson = personbehavior.pcucodeperson AND person.pid = personbehavior.pid
WHERE
person.typelive IN (0,1,3)
AND person.dischargetype = 9
AND person.pid NOT IN (SELECT persondeath.pid FROM persondeath)
AND getAgeYearNum (birth,'20140430') BETWEEN 15 AND 19
AND RIGHT(village.villcode,2) != '00'
GROUP BY
หมู่ที่
UNION
SELECT
'sum',
COUNT(DISTINCT person.pid) AS 'จำนวนเป้าหมายประชากร อายุ 15-19 ปี(คน)',
SUM(CASE WHEN personbehavior.wisky BETWEEN '1' AND '6' THEN '1' ELSE NULL END) AS 'ดื่ม',
round(SUM(CASE WHEN personbehavior.wisky BETWEEN '1' AND '6' THEN '1' ELSE NULL END) * 100 / COUNT(DISTINCT person.pid),2) AS 'ร้อยละ',
SUM(CASE WHEN personbehavior.wisky = '0' THEN '1' ELSE NULL END) AS 'ไม่ดื่ม',
round(SUM(CASE WHEN personbehavior.wisky = '0' THEN '1' ELSE NULL END) * 100 / COUNT(DISTINCT person.pid),2) AS 'ร้อยละ'
FROM
village
INNER JOIN house ON house.pcucode = village.pcucode AND house.villcode = village.villcode
INNER JOIN person ON person.pcucodeperson = house.pcucode AND person.hcode = house.hcode
LEFT JOIN personbehavior ON person.pcucodeperson = personbehavior.pcucodeperson AND person.pid = personbehavior.pid
WHERE
person.typelive IN (0,1,3)
AND person.dischargetype = 9
AND person.pid NOT IN (SELECT persondeath.pid FROM persondeath)
AND getAgeYearNum (birth,'20140430') BETWEEN 15 AND 19
AND RIGHT(village.villcode,2) != '00'