MariaDB 10.6

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version()


10.6.9-MariaDB-1:10.6.9+maria~deb11


CREATE TABLE dummy (
    nama varchar(10) not null,
    kelas varchar(3) not null,
    pembayaran varchar(10) not null,
    nominal int
);
INSERT INTO dummy VALUES
('Ahmad', 'X A', 'Bulanan', 50000),
('Ahmad', 'X A', 'Bulanan', 50000),
('Abdul', 'X B', 'Bulanan', 50000),
('Abdul', 'X B', 'Bulanan', 50000),
('Adi', 'X B', 'Bulanan', 50000),
('Adi', 'X B', 'Bulanan', 25000),
('Budi', 'X A', 'Bulanan', 50000),
('Budi', 'X A', 'Bulanan', 50000);

Records: 8  Duplicates: 0  Warnings: 0


SELECT * FROM dummy;


nama
kelas
pembayaran
nominal


Ahmad
X A
Bulanan
50000

Ahmad
X A
Bulanan
50000

Abdul
X B
Bulanan
50000

Abdul
X B
Bulanan
50000

Adi
X B
Bulanan
50000

Adi
X B
Bulanan
25000

Budi
X A
Bulanan
50000

Budi
X A
Bulanan
50000


SELECT 
  a.kelas, 
  COUNT(a.nama) AS jumlah_siswa, 
  SUM(if(a.nominal=100000, 1, 0)) AS siswa_lunas
FROM (
  SELECT nama, kelas, SUM(nominal) AS nominal
  FROM dummy
  GROUP BY nama
) AS a
GROUP BY kelas;


kelas
jumlah_siswa
siswa_lunas


X A
2
2

X B
2
1

nama	kelas	pembayaran	nominal
Ahmad	X A	Bulanan	50000
Ahmad	X A	Bulanan	50000
Abdul	X B	Bulanan	50000
Abdul	X B	Bulanan	50000
Adi	X B	Bulanan	50000
Adi	X B	Bulanan	25000
Budi	X A	Bulanan	50000
Budi	X A	Bulanan	50000