#KEHADIRAN
SELECT nim,kdprodi,kdstsemester,
sum(H) as H,
sum(T) as T,
sum(S) as S,
sum(I) as I,
sum(A) as A,
sum(K) as K
FROM
(
SELECT A.nim,A.kdprodi,A.kdstsemester,A.kdmk,A.idstabsenmhs,B.kdstabsensimhs,
CASE WHEN kdstabsensimhs='H' THEN 1 else 0 end as H,
CASE WHEN kdstabsensimhs='T' THEN 1 else 0 end as T,
CASE WHEN kdstabsensimhs='S' THEN 1 else 0 end as S,
CASE WHEN kdstabsensimhs='I' THEN 1 else 0 end as I,
CASE WHEN kdstabsensimhs='A' THEN 1 else 0 end as A,
CASE WHEN kdstabsensimhs='K' THEN 1 else 0 end as K
FROM
(
select nim,kdprodi,kdstsemester,kdmk,p1 as idstabsenmhs, 'p1'as pt from absenmhs
UNION
select nim,kdprodi,kdstsemester,kdmk,p2 as idstabsenmhs, 'p2'as pt from absenmhs
UNION
select nim,kdprodi,kdstsemester,kdmk,p3 as idstabsenmhs, 'p3'as pt from absenmhs
UNION
select nim,kdprodi,kdstsemester,kdmk,p4 as idstabsenmhs, 'p4'as pt from absenmhs
UNION
select nim,kdprodi,kdstsemester,kdmk,p5 as idstabsenmhs, 'p5'as pt from absenmhs
UNION
select nim,kdprodi,kdstsemester,kdmk,p6 as idstabsenmhs, 'p6'as pt from absenmhs
UNION
select nim,kdprodi,kdstsemester,kdmk,p7 as idstabsenmhs, 'p7'as pt from absenmhs
UNION
select nim,kdprodi,kdstsemester,kdmk,p8 as idstabsenmhs, 'p8'as pt from absenmhs
UNION
select nim,kdprodi,kdstsemester,kdmk,p9 as idstabsenmhs, 'p9'as pt from absenmhs
UNION
select nim,kdprodi,kdstsemester,kdmk,p10 as idstabsenmhs, 'p10'as pt from absenmhs
UNION
select nim,kdprodi,kdstsemester,kdmk,p11 as idstabsenmhs, 'p11'as pt from absenmhs
UNION
select nim,kdprodi,kdstsemester,kdmk,p12 as idstabsenmhs, 'p12'as pt from absenmhs
UNION
select nim,kdprodi,kdstsemester,kdmk,p13 as idstabsenmhs, 'p13'as pt from absenmhs
UNION
select nim,kdprodi,kdstsemester,kdmk,p14 as idstabsenmhs, 'p14'as pt from absenmhs
UNION
select nim,kdprodi,kdstsemester,kdmk,p15 as idstabsenmhs, 'p15'as pt from absenmhs
UNION
select nim,kdprodi,kdstsemester,kdmk,p16 as idstabsenmhs, 'p16'as pt from absenmhs
)A
LEFT JOIN stabsenmhs B ON B.idstabsenmhs=A.idstabsenmhs
)X
GROUP BY nim,kdprodi,kdstsemester