Fungsi agregate adalah fungsi yang menerima sejumlah nilai dan mengembalikannya dengan hasil nilai tunggal, contohnya seperti menentukan nilai terendah, tertinggi, nilai rata-rata pada satu set nilai, dan menghitung jumlah record/baris data yang tersimpan. Ada beberapa fungsi agregate yang sering digunakan pada MySQL, diantaranya:
| Fungsi | Keterangan |
|---|---|
| Count | Menghitung jumlah record/baris data yang tersimpan |
| SUM | Menjumlahkan total nilai yang tersimpan pada sebuah kolom |
| AVG | Mencari untuk mencari nilai rata-rata pada sebuah kolom |
| Min | Mencari nilai terendah |
| Max | Mencari nilai tertinggi |
Adapun contoh penggunaannya adalah sebagai berikut:
mysql> Select gaji_pokok from gaji order by gaji_pokok asc;
+------------+
| gaji_pokok |
+------------+
| 6100000 |
| 7100000 |
| 7100000 |
..............
| 8100000 |
+------------+
30 rows in set (0.00 sec)Note: Data di persingkat.
Pada contoh diatas kita ingin menentukan gaji pokok terkecil, tertinggi, rata-rata ataupun total keseluruhan gaji pokok, kita mungkin dapat menggunakan klausa order namun cara tersebut tidak begitu efektif karena banyaknya nilai yang dikembalikan maka dari itu kita bisa menggunakan fungsi agregat. Berikut adalah bentuk umum dan contoh penggunaan fungsi agregat mysql:
SUM
mysql> select sum(nama_kolom) {as nama_alias_tabel} from tabel;
mysql> select sum(gaji_pokok) as "Total Gaji" from gaji;
+------------+
| Total Gaji |
+------------+
| 430500000 |
+------------+
1 row in set (0.01 sec)AVG
mysql> select avg(gaji_pokok) as "Rata-rata Gaji" from gaji;
+----------------+
| Rata-rata Gaji |
+----------------+
| 14350000.0000 |
+----------------+
1 row in set (0.03 sec)Min
mysql> select min(gaji_pokok) as "Gaji Terendah" from gaji;
+---------------+
| Gaji Terendah |
+---------------+
| 6100000 |
+---------------+
1 row in set (0.00 sec)Max
mysql> select max(gaji_pokok) as "Gaji Tertinggi" from gaji;
+----------------+
| Gaji Tertinggi |
+----------------+
| 26100000 |
+----------------+
1 row in set (0.00 sec)Adapun count digunakan untuk menghitung total record yang tersimpan dari suatu tabel, selain itu penulis juga menggunakan klausa as untuk memberi nama lain/alias pada kolom sehingga membuatnya menjadi lebih mudah untuk diidentifikasi, contohnya seperti berikut:
mysql> select count(*) from gaji;
+----------+
| count(*) |
+----------+
| 30 |
+----------+
1 row in set (0.03 sec)
mysql> select count(*) as "Total Record Tersimpan" from gaji;
+------------------------+
| Total Record Tersimpan |
+------------------------+
| 30 |
+------------------------+
1 row in set (0.00 sec)Lalu, bagaimana jika kita ingin mencari nilai yang lebih besar/kurang/diatas nilai maksimum/minimum/rata-rata? Agar lebih memahaminya, coba perhatikan contoh berikut terlebih dahulu:
mysql> select gaji_pokok from gaji where gaji_pokok >= avg(gaji_pokok);
ERROR 1111 (HY000): Invalid use of group function
mysql> select gaji_pokok from gaji where gaji_pokok >= max(gaji_pokok);
ERROR 1111 (HY000): Invalid use of group function
mysql> select gaji_pokok from gaji where gaji_pokok >= min(gaji_pokok);
ERROR 1111 (HY000): Invalid use of group functionError tersebut terjadi karena pada dasarnya fungsi agregat hanya mengembalikan nilai tunggal dari satu "set" nilai, untuk itu jika ingin melakukan pencarian data dimana fungsi agregat sebagai kondisinya maka perlu menggunakan fungsi tambahan yakni dengan subquery atau klausa Group By dan Having seperti contoh berikut:
Lebih besar dari
mysql> select max(gaji_pokok) from gaji;
+-----------------+
| max(gaji_pokok) |
+-----------------+
| 26100000 |
+-----------------+
1 row in set (0.00 sec)
mysql> select dosen_id, gaji_pokok from gaji where gaji_pokok>=(select max(gaji_pokok) from gaji);
+----------+------------+
| dosen_id | gaji_pokok |
+----------+------------+
| 17 | 26100000 |
+----------+------------+
1 row in set (0.00 sec)
mysql> select dosen_id, gaji_pokok from gaji group by dosen_id having max(gaji_pokok)>=26100000;
+----------+------------+
| dosen_id | gaji_pokok |
+----------+------------+
| 17 | 26100000 |
+----------+------------+
1 row in set (0.00 sec)Kurang dari
mysql> select min(gaji_pokok) from gaji;
+-----------------+
| min(gaji_pokok) |
+-----------------+
| 6100000 |
+-----------------+
1 row in set (0.00 sec)
mysql> select dosen_id, gaji_pokok from gaji where gaji_pokok<=(select min(gaji_pokok) from gaji);
+----------+------------+
| dosen_id | gaji_pokok |
+----------+------------+
| 27 | 6100000 |
+----------+------------+
1 row in set (0.00 sec)
mysql> select dosen_id, gaji_pokok from gaji group by dosen_id having min(gaji_pokok)<=6100000;
+----------+------------+
| dosen_id | gaji_pokok |
+----------+------------+
| 27 | 6100000 |
+----------+------------+
1 row in set (0.00 sec)Diatas rata-rata
mysql> select dosen_id, gaji_pokok from gaji where gaji_pokok >= (select avg(gaji_pokok) from gaji);
+----------+------------+
| dosen_id | gaji_pokok |
+----------+------------+
| 8 | 15300000 |
| 10 | 18100000 |
| 11 | 18100000 |
| 12 | 19100000 |
| 13 | 20100000 |
| 14 | 22100000 |
| 15 | 25100000 |
| 16 | 23100000 |
| 17 | 26100000 |
| 18 | 22100000 |
| 19 | 18100000 |
| 20 | 17100000 |
| 21 | 15100000 |
| 30 | 18100000 |
+----------+------------+
14 rows in set (0.00 sec)
mysql> select dosen_id, gaji_pokok from gaji group by dosen_id having avg(gaji_pokok)>=14350000.000;
+----------+------------+
| dosen_id | gaji_pokok |
+----------+------------+
| 8 | 15300000 |
| 10 | 18100000 |
| 11 | 18100000 |
| 12 | 19100000 |
| 13 | 20100000 |
| 14 | 22100000 |
| 15 | 25100000 |
| 16 | 23100000 |
| 17 | 26100000 |
| 18 | 22100000 |
| 19 | 18100000 |
| 20 | 17100000 |
| 21 | 15100000 |
| 30 | 18100000 |
+----------+------------+
14 rows in set (0.00 sec)Dari contoh diatas dapat dilihat bahwa jika kita ingin menggunakan klausa where maka kita memerlukan subquery sedangkan jika tidak ingin menggunakan where maka kita perlu menggunakan klausa having yang didahului oleh group by, untuk pemabahasan materi having lebih lengkap dapat dilihat pada materi having.