SELECT
pcucode,
off_name,
COUNT(*) AS เป้าหมาย,
SUM(CASE WHEN t.bcg="1" THEN 1 ELSE 0 END) AS BCG,
SUM(CASE WHEN t.hbv1="1" THEN 1 ELSE 0 END) AS HBV1,
SUM(CASE WHEN t.opv3="1" THEN 1 ELSE 0 END) AS OPV3,
SUM(CASE WHEN t.dhb3="1" THEN 1 ELSE 0 END) AS DTPHB3,
SUM(CASE WHEN t.mmr="1" THEN 1 ELSE 0 END) AS MMR,
SUM(CASE WHEN t.opv4="1" THEN 1 ELSE 0 END) AS OPV4,
SUM(CASE WHEN t.dtp4="1" THEN 1 ELSE 0 END) AS DTP4,
SUM(CASE WHEN t.je052_j11="1" THEN 1 ELSE 0 END) AS JE052_J11,
SUM(CASE WHEN t.mmr2="1" THEN 1 ELSE 0 END) AS MMR2,
SUM(CASE WHEN t.je053_j11_12="1" THEN 1 ELSE 0 END) AS JE053_J11_12,
SUM(CASE WHEN t.opv5="1" THEN 1 ELSE 0 END) AS OPV5,
SUM(CASE WHEN t.dtp5="1" THEN 1 ELSE 0 END) AS DTP5,
SUM(CASE WHEN t.bcg="1"and t.hbv1="1" AND t.opv3="1" and t.dhb3="1" AND t.mmr="1"
and t.opv4="1"and t.dtp4="1" AND t.je052_j11="1" and t.mmr2="1" AND t.je053_j11_12="1" and t.opv5="1"and t.dtp5="1" THEN 1 ELSE 0 END) AS EPI_COVER,
SUM(CASE WHEN t.bcg="1"and t.hbv1="1" AND t.opv3="1" and t.dhb3="1" AND t.mmr="1"
and t.opv4="1"and t.dtp4="1" AND t.je052_j11="1" and t.mmr2="1" AND t.je053_j11_12="1" and t.opv5="1"and t.dtp5="1" THEN 1 ELSE 0 END) / COUNT(*)*100 as percen
FROM tmp_prm_vacc_52561 AS t
INNER JOIN co_office on off_id = pcucode
GROUP BY pcucode
union
select "ภาพรวมทั้งคัพ" as pcucode,"" as off_name,
COUNT(*) AS เป้าหมาย,
SUM(CASE WHEN t.bcg="1" THEN 1 ELSE 0 END) AS BCG,
SUM(CASE WHEN t.hbv1="1" THEN 1 ELSE 0 END) AS HBV1,
SUM(CASE WHEN t.opv3="1" THEN 1 ELSE 0 END) AS OPV3,
SUM(CASE WHEN t.dhb3="1" THEN 1 ELSE 0 END) AS DTPHB3,
SUM(CASE WHEN t.mmr="1" THEN 1 ELSE 0 END) AS MMR,
SUM(CASE WHEN t.opv4="1" THEN 1 ELSE 0 END) AS OPV4,
SUM(CASE WHEN t.dtp4="1" THEN 1 ELSE 0 END) AS DTP4,
SUM(CASE WHEN t.je052_j11="1" THEN 1 ELSE 0 END) AS JE052_J11,
SUM(CASE WHEN t.mmr2="1" THEN 1 ELSE 0 END) AS MMR2,
SUM(CASE WHEN t.je053_j11_12="1" THEN 1 ELSE 0 END) AS JE053_J11_12,
SUM(CASE WHEN t.opv5="1" THEN 1 ELSE 0 END) AS OPV5,
SUM(CASE WHEN t.dtp5="1" THEN 1 ELSE 0 END) AS DTP5,
SUM(CASE WHEN t.bcg="1"and t.hbv1="1" AND t.opv3="1" and t.dhb3="1" AND t.mmr="1"
and t.opv4="1"and t.dtp4="1" AND t.je052_j11="1" and t.mmr2="1" AND t.je053_j11_12="1" and t.opv5="1"and t.dtp5="1" THEN 1 ELSE 0 END) AS EPI_COVER,
SUM(CASE WHEN t.bcg="1"and t.hbv1="1" AND t.opv3="1" and t.dhb3="1" AND t.mmr="1"
and t.opv4="1"and t.dtp4="1" AND t.je052_j11="1" and t.mmr2="1" AND t.je053_j11_12="1" and t.opv5="1"and t.dtp5="1" THEN 1 ELSE 0 END) / COUNT(*)*100 as percen
FROM tmp_prm_vacc_52561 AS t
INNER JOIN co_office on off_id = pcucode