Tutorial MySQL: Fungsi Aggregate

Dalam sesi tutorial database MySQL kali ini kita akan membahas tentang apa itu fungsi agregate beserta contoh kasusnya. Fungsi agregate ini sangat diperlukan dalam penyajian data pada database yang memiliki banyak data, dimana data tersebut tersebar pada beberapa tabel.

Misalnya kita memiliki data pada beberapa tabel, kemudian kita perlu melakukan perhitungan nilai rata-rata ataupun jumlah dari suatu nilai terhadap data-data yang terkandung dalam beberapa tabel tersebut. Dalam hal ini data pada suatu tabel terkait dengan data pada tabel lain.Untuk melakukan operasi tersebut, kita dapat menggunakan fungsi aggregat.

Apa itu fungsi aggregate ?

Fungsi aggregat adalah suatu fungsi yang akan meringkas beberapa data pada field (kolum) menjadi nilai tunggal. Fungsi aggregate sering digunakan bersamaan dengan query Group By untuk pengelompokan berdasarkan kolom tertentu.

Jenis-jenis fungsi aggregate 

Berdasarkan standar ISO SQL ( ISO Standard SQL), fungsi aggregate terdiri dari :

• COUNT(*)     : Mendapatkan jumlah baris. 
• SUM(Kolom) : Mendapatkan penjumlahan kolom.
• MAX(Kolom) : Mendapatkan nilai maksimal (tertinggi)
• MIN(Kolom)  : Mendapatkan nilai minimal (terendah).
• AVG(Kolom) : Melakukan perhitungan nilai rata-rata.

Dalam percobaab berikut, kita akan menggunakan DBMS MySQL dengan Interface Client : SQLyog.

Langkah-langkah pembuatan database

Buatlah sebuah database yang bernama kuliahan pada query editor SQLyog seperti gambar berikut:

.

Buatlah tabel yang diberi nama mahasiswa seperti query dibawah ini :

CREATE TABLE `mahasiswa` (
  `NIM` int(11) NOT NULL,
  `Nama` varchar(20) NOT NULL,
  `Jenis_Kelamin` enum('L','P') NOT NULL,
  PRIMARY KEY (`NIM`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Kemudian buatlah tabel yang diberi nama mata_kuliah seperti query dibawah ini :

CREATE TABLE `mata_kuliah` (
  `Kode` varchar(6) NOT NULL,
  `Nama_Matkul` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`Kode`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Terakhir, kita membuat tabel hasil seperti query dibawah ini :

CREATE TABLE `hasil` (
  `NIM` int(11) DEFAULT NULL,
  `Kode_Matkul` varchar(6) DEFAULT NULL,
  `Nilai` int(3) DEFAULT NULL,
  KEY `NIM` (`NIM`),
  KEY `Kode_Matkul` (`Kode_Matkul`),
  CONSTRAINT `hasil_ibfk_1` FOREIGN KEY (`NIM`) REFERENCES `mahasiswa` (`NIM`),
  CONSTRAINT `hasil_ibfk_2` FOREIGN KEY (`Kode_Matkul`) REFERENCES `mata_kuliah` (`Kode`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Pengisian data pada database Kuliahan

Isikan data pada tabel mahasiswa seperti query berikut :

insert  into `mahasiswa`(`NIM`,`Nama`,`Jenis_Kelamin`) 
values (11001,'Andi Marta','L'),
(11002,'Diana Suri','P'),
(11003,'Tuti Mirna','P'),
(11004,'Putra Riza','L'),
(11005,'Reza Palmuri','L'),
(11006,'Gunawan Wahab','L'),
(11007,'Vira Luswita','P');

Sehingga kita akan menghasilkan tabel mahasiswa seperti gambar berikut :

Kemudian, kita isi data pada tabel mata_kuliah seperti query dibawah ini :

insert  into `mata_kuliah`(`Kode`,`Nama_Matkul`) 
values 
('IF1201','Pemrograman C++'),
('IF1203','Pemrograman Java'),
('IF1205','Pemrograman Web'),
('IF1301','Rekayasa Perangkat L'),
('IF1401','Jaringan Komputer'),
('IF1603','Basis Data');

Sehingga tabel mata_kuliah mengandung data seperti berikut :

Terakhir, kita akan mengisi data pada tabel hasil seperti query dibawah ini :

insert  into `hasil`(`NIM`,`Kode_Matkul`,`Nilai`)
values (11001,'IF1201',80),
(11001,'IF1203',65),(11001,'IF1205',96),
(11001,'IF1301',70),(11001,'IF1401',65),
(11001,'IF1603',83),(11002,'IF1201',65),
(11002,'IF1203',65),(11002,'IF1205',80),
(11002,'IF1301',90),(11002,'IF1401',80),
(11002,'IF1603',65),(11003,'IF1201',60),
(11003,'IF1203',87),(11003,'IF1205',90),
(11003,'IF1301',80),(11003,'IF1401',70),
(11003,'IF1603',60),(11004,'IF1201',60),
(11004,'IF1203',89),(11004,'IF1205',76),
(11004,'IF1301',78),(11004,'IF1401',67),
(11004,'IF1603',87),(11005,'IF1201',80),
(11005,'IF1203',89),(11005,'IF1205',90),
(11005,'IF1301',88),(11005,'IF1401',85),
(11005,'IF1603',94),(11006,'IF1201',78),
(11006,'IF1203',88),(11006,'IF1205',87),
(11006,'IF1301',98),(11006,'IF1401',87),
(11007,'IF1201',90),(11007,'IF1203',90),
(11007,'IF1205',87),(11007,'IF1301',90),
(11007,'IF1401',80),(11007,'IF1603',87);

Contoh Kasus 1#

Menampilkan kolom nim, nama, nama_matkul dan nilai untuk tiap-tiap mata kuliah.

SELECT m.nim,m.nama, k.Nama_Matkul, h.`Nilai`
FROM mahasiswa m, mata_kuliah k, hasil h
WHERE m.`NIM`=h.`NIM` AND k.`Kode`=h.`Kode_Matkul`
ORDER BY h.`NIM`

Output dari query diatas :

Seperti yang terlihat pada gambar diatas, maka akan ditampilkan semua nilai per mata kuliah yang diikuti oleh masing-masing mahasiswa. Pada gambar hanya tertampilkan sampai dengan nim 11005, sesungguhnya akan tertampilkan sampai nim 11007. Hal ini karena snipping tool tidak dapat mencapture gambar yang scroll down.

Untuk kasus no.1#, kita tidak perlu menggunakan aggregate. Karena permintaannya hanya nilai per mata kuliah untuk masing-masing mahasiswa. Yang menjadi perhatian kita, query ini menggunakan tiga tabel, karena memilih kolom nim dan nama pada tabel mahasiswa, kolom Nama_matkul pada tabel mata_kuliah dan kolom nilai pada tabel hasil. Kemudian kita menggunakan klausa where pada tabel mahasiswa dan hasil dengan penghubung nim yang bertujuan untuk dapat menampilkan nama dari si pemilik nim tersebut. Seperti yang dapat kita lihat pada tabel hasil, tidak terdapat kolom nama.

Hal yang sama juga kita terapkan klausa where untuk tabel mata_kul dan hasil dengan penghubungnya adalah kode yang bertujuan untuk mendapatkan Nama_Matkul, karena ditabel hasil hanya terdapat kolom kode saja.

Contoh Kasus 2#

Menampilkan kolom nim, nama dan nilai-rata untuk masing-masing mahasiswa.

SELECT m.nim,m.nama,  AVG(h.`Nilai`) AS nila_rata_rata
FROM mahasiswa m,  hasil h
WHERE m.`NIM`=h.`NIM` 
GROUP BY h.`NIM`

Output dari query diatas :

Pada kasus no.2#, output memperlihatkan nilai rata-rata mahasiswa yang mengikuti 6  matakuliah. Kita masih dapat menggunakan klausa WHERE, karena peran klausa WHERE disini hanya untuk mendapatkan kolom nama pada tabel mahasiswa, jadi bisa dikatakan fungsi WHERE dalam kasus ini bukan sebagai filter data yang menggunakan fungsi aggregat.

Contoh Kasus 3#

Menampilkan kolom nim, nama dan nilai-rata untuk masing-masing mahasiswa dengan kondisi nilai rata-rata yang ditampilkan harus lebih besar dari 80.

SELECT m.nim,m.nama,  AVG(h.`Nilai`) AS nila_rata_rata
FROM mahasiswa m,  hasil h
WHERE m.`NIM`=h.`NIM` 
GROUP BY h.`NIM`
HAVING AVG(h.`Nilai`) >80

Output dari query diatas :

Perhatikan query pada Kasus no.2# dan Kasus no.3#, karena permintaanya hanya nilai rata-rata yang lebih besar 80 yang boleh ditampilkan, maka kita harus menggunakan klausa HAVING. Dalam aggregat HAVING memiliki tujuan sama dengan WHERE. Namun klausa WHERE tidak berjalan jika kita memfilter data yang menggunakan fungsi aggregat.

Contoh Kasus 4#

Menampilkan kolom nim, nama dan beserta nilai tertinggi dan nilai terendah yang diraih oleh masing-masing mahasiswa.

SELECT m.nim,m.nama,  MAX(h.`Nilai`) AS Nilai_tertinggi,
MIN(h.`Nilai`) AS Nilai_terendah
FROM mahasiswa m,  hasil h
WHERE m.`NIM`=h.`NIM` 
GROUP BY h.`NIM`

Output dari query diatas :

Contoh Kasus 5#

Menampilkan kolom Kode Mata Kuliah, Nama Mata Kuliah dan Nilai rata-rata untuk tiap mata kuliah yang dikelompokkan berdasarkan Kode Mata Kuliah.

SELECT H.`Kode_Matkul`, M.`Nama_Matkul`, AVG(H.`Nilai`) AS RATA_RATA
FROM hasil H, mata_kuliah M
WHERE h.`Kode_Matkul`= M.`Kode`
GROUP BY Kode_Matkul

Output dari query diatas :

Pada contoh kasus 5, terdapat 6 Mata Kuliah yang diikuti oleh 7 Mahasiswa, kita ingin mencari nilai rata untuk tiap mata kuliah yang diikuti oleh 7 Mahasiswa tersebut. Perhatikan scriptnya, kita masih memfilternya menggunakan klausa WHERE, karena kita tidak memfilter suatu kolom yang menggunakan Aggregat. Jika memfilter Nilai rata-rata yang lebih besar 80, disini baru kita menggunakan Klausa HAVING , seperti contoh Kasus#3.

Contoh Kasus 6#

Menampilkan kolom Kode Mata Kuliah, Nama Mata Kuliah dan Jumlah keseluruhan nilai untuk tiap mata kuliah yang dikelompokkan berdasarkan Kode Mata Kuliah.

SELECT H.`Kode_Matkul`, M.`Nama_Matkul`, SUM(H.`Nilai`) AS RATA_RATA
FROM hasil H, mata_kuliah M
WHERE h.`Kode_Matkul`= M.`Kode`
GROUP BY Kode_Matkul

Output dari query diatas :

Dalam kasus#6, kita menjumlah nilai per matakuliah yang diikuti 7 mahasiswa. 

Untuk fungsi aggregate Count, hanya mengembalikan nilai balik berupa jumlah baris. Seandainya Mahasiswa yang mengikuti perkuliahan ada 7 orang, maka jika kita ingin berapa orang yang mengikuti perkuliahan, kita dapat menggunakan fungsi Count.