rekap absensi

USE test;

DROP TABLE IF EXISTS absensi_kelas;
CREATE TABLE IF NOT EXISTS absensi_kelas(
  absensi_id INT,
  kelas_id INT
);

INSERT INTO absensi_kelas
VALUES
(1,1),
(2,1),
(3,2),
(4,2),
(5,3),
(6,3);

DROP TABLE IF EXISTS absensi;
CREATE TABLE IF NOT EXISTS absensi(
  absensi_id INT,
  keterangan CHAR(1)
);

INSERT INTO absensi
VALUES
(1,'H'),
(1,'A'),
(1,'S'),
(1,'H'),
(1,'H'),
(1,'I'),
(1,'H'),
(1,'I'),
(2,'H'),
(2,'A'),
(2,'S'),
(2,'H'),
(2,'H'),
(2,'H'),
(2,'H'),
(2,'H'),
(2,'I'),
(3,'H'),
(3,'A'),
(3,'S'),
(3,'H'),
(3,'A'),
(4,'H'),
(4,'H'),
(4,'H'),
(4,'I'),
(5,'H'),
(5,'A'),
(5,'S'),
(5,'S'),
(5,'H'),
(5,'H'),
(5,'I'),
(5,'I'),
(5,'I');

SELECT
  a.kelas_id,
  SUM(IF(b.keterangan='S',1,0)) AS S,
  SUM(IF(b.keterangan='I',1,0)) AS I,
  SUM(IF(b.keterangan='A',1,0)) AS A
FROM
  absensi_kelas a
  LEFT JOIN absensi b USING(absensi_id)
GROUP BY a.kelas_id;