Fungsi Agregat

“Berapa total omzet bulan ini?”, “Berapa rata-rata nilai ujian?”, “Berapa transaksi terjadi?” — pertanyaan-pertanyaan ini tidak dijawab dengan membaca baris satu per satu, melainkan dengan meringkas banyak baris menjadi satu angka. Fungsi agregat (SUM, AVG, MIN, MAX, COUNT) adalah alat untuk itu: fungsi-fungsi ini menerima satu kolom berisi banyak nilai dan mengembalikan satu nilai rangkuman. Pada chapter ini kita berlatih agregasi paling dasar — seluruh tabel menjadi satu baris — memakai data penjualan kecil, lalu kita selidiki dua detail yang paling sering menjebak: DISTINCT di dalam COUNT, dan perlakuan agregat terhadap NULL yang bisa membuat SUM menghasilkan NULL.

Menyiapkan Tabel Latihan #

Kita siapkan dua tabel: penjualan berisi transaksi lengkap, dan stok_lama berisi kolom yang seluruh barisnya NULL untuk percobaan khusus:

DROP DATABASE IF EXISTS belajar_fungsi;
CREATE DATABASE belajar_fungsi;
USE belajar_fungsi;

Output yang Dihasilkan:

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Database changed

CREATE TABLE penjualan (
  id INT AUTO_INCREMENT PRIMARY KEY,
  produk VARCHAR(50) NOT NULL,
  kota VARCHAR(30) NOT NULL,
  jumlah INT NOT NULL,
  subtotal DECIMAL(10,2) NOT NULL
);

INSERT INTO penjualan (produk, kota, jumlah, subtotal) VALUES
  ('Buku Tulis', 'Bandung', 10, 45000.00),
  ('Pensil 2B', 'Bandung', 20, 60000.00),
  ('Kertas A4', 'Surabaya', 2, 500000.00),
  ('Penghapus', 'Surabaya', 5, 12500.00),
  ('Buku Tulis', 'Yogyakarta', 8, 36000.00),
  ('Spidol Whiteboard', 'Medan', 3, 18000.00),
  ('Kertas A4', 'Medan', 1, 250000.00);

Output yang Dihasilkan:

Query OK, 0 rows affected (0.00 sec)

Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

CREATE TABLE stok_lama (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nama VARCHAR(50) NOT NULL,
  harga_lama DECIMAL(10,2) NULL
);

INSERT INTO stok_lama (nama, harga_lama) VALUES
  ('Buku Gambar A4', NULL),
  ('Rautan', NULL),
  ('Stipo', NULL);

Output yang Dihasilkan:

Query OK, 0 rows affected (0.00 sec)

Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

Tabel penjualan memuat tujuh transaksi di empat kota. Tabel stok_lama menirukan hasil migrasi data lama yang kolom harganya belum terisi — tiga baris, semua harga_lama-nya NULL.

Merangkum Seluruh Tabel Menjadi Satu Baris #

Satu query bisa memanggil beberapa agregat sekaligus. Tanpa GROUP BY, seluruh tabel diringkas menjadi satu baris hasil:

SELECT COUNT(*) AS jumlah_transaksi,
       SUM(jumlah) AS total_barang,
       SUM(subtotal) AS omzet,
       AVG(subtotal) AS rata_rata,
       MIN(subtotal) AS transaksi_terkecil,
       MAX(subtotal) AS transaksi_terbesar
FROM penjualan;

Output yang Dihasilkan:

+------------------+--------------+-----------+---------------+--------------------+--------------------+
| jumlah_transaksi | total_barang | omzet     | rata_rata     | transaksi_terkecil | transaksi_terbesar |
+------------------+--------------+-----------+---------------+--------------------+--------------------+
|                7 |           49 | 921500.00 | 131642.857143 |           12500.00 |          500000.00 |
+------------------+--------------+-----------+---------------+--------------------+--------------------+
1 row in set (0.00 sec)

Enam agregat bekerja pada kolom yang berbeda dan menghasilkan satu baris: tujuh transaksi, 49 barang terjual, omzet 921500, rata-rata 131642.857143, transaksi terkecil 12500.00, dan terbesar 500000.00. Perhatikan angka rata_rata: AVG atas kolom DECIMAL(10,2) menghasilkan DECIMAL dengan enam digit pecahan — MySQL memperluas skala hasilnya demi presisi pembagian. Bila tampilan laporan butuh dua desimal, bungkus dengan ROUND(AVG(subtotal), 2) seperti yang kita latih di chapter Fungsi Numerik.

Nilai COUNT(*) menghitung baris tanpa peduli isi. Setiap agregat lain (SUM, AVG, MIN, MAX, dan COUNT(kolom)) bekerja pada nilai kolom — dan di sinilah NULL mulai bermain peran, seperti akan kita lihat beberapa bagian berikutnya.

Menghitung Nilai Berbeda dengan DISTINCT #

COUNT(*) menghitung baris, sedangkan COUNT(DISTINCT kolom) menghitung nilai berbeda di kolom itu:

SELECT COUNT(*) AS total_baris,
       COUNT(DISTINCT kota) AS kota_berbeda,
       COUNT(DISTINCT produk) AS produk_berbeda
FROM penjualan;

Output yang Dihasilkan:

+-------------+--------------+----------------+
| total_baris | kota_berbeda | produk_berbeda |
+-------------+--------------+----------------+
|           7 |            4 |              5 |
+-------------+--------------+----------------+
1 row in set (0.00 sec)

Tujuh baris transaksi hanya berasal dari empat kota dan lima produk — Bandung dan Kertas A4 sama-sama muncul dua kali tetapi hanya dihitung sekali. Kata kunci DISTINCT juga bisa dipakai di SUM dan AVG (misalnya SUM(DISTINCT jumlah)), meski kebutuhannya jauh lebih jarang. Pola COUNT(DISTINCT ...) inilah cara cepat menjawab “ada berapa kategori unik di tabel ini?” tanpa membaca data satu per satu.

Agregat Mengabaikan NULL #

Fungsi AVG, SUM, MIN, dan MAX melewatkan nilai NULL sepenuhnya — seolah barisnya tidak punya nilai. Kita butuh satu tabel lagi dengan lubang di tengah data untuk melihatnya:

CREATE TABLE penilaian (
  id INT AUTO_INCREMENT PRIMARY KEY,
  siswa VARCHAR(40) NOT NULL,
  skor DECIMAL(5,2) NULL
);

INSERT INTO penilaian (siswa, skor) VALUES
  ('Budi', 80.00),
  ('Siti', 90.00),
  ('Tono', NULL),
  ('Dewi', 70.00);

Output yang Dihasilkan:

Query OK, 0 rows affected (0.00 sec)

Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

SELECT COUNT(*) AS jumlah_siswa,
       COUNT(skor) AS skor_terisi,
       AVG(skor) AS rata_rata
FROM penilaian;

Output yang Dihasilkan:

+--------------+-------------+-----------+
| jumlah_siswa | skor_terisi | rata_rata |
+--------------+-------------+-----------+
|            4 |           3 | 80.000000 |
+--------------+-------------+-----------+
1 row in set (0.00 sec)

Empat siswa tercatat, tetapi COUNT(skor) hanya tiga: COUNT(kolom) tidak menghitung NULL. Rata-ratanya 80.000000 — hasil dari (80 + 90 + 70) / 3, bukan dibagi empat. Perilaku ini bisa masuk akal (nilai yang belum ada memang tidak layak dihitung) atau tidak (Tono tetap siswa yang seharusnya “menganggu” rata-rata dengan nol) tergantung kebutuhan laporan — yang penting, kita memilihnya secara sadar dan bukan karena kejutan.

SUM atas Kolom yang Semuanya NULL #

Sekarang ujian sesungguhnya: apa hasil SUM ketika tidak ada satu pun nilai untuk dijumlah? Tabel stok_lama menyimpan jawabannya:

SELECT COUNT(*) AS jumlah_baris,
       COUNT(harga_lama) AS nilai_terisi,
       SUM(harga_lama) AS total_harga
FROM stok_lama;

Output yang Dihasilkan:

+--------------+--------------+-------------+
| jumlah_baris | nilai_terisi | total_harga |
+--------------+--------------+-------------+
|            3 |            0 |        NULL |
+--------------+--------------+-------------+
1 row in set (0.00 sec)

SUM menghasilkan NULL, bukan 0. Logikanya konsisten dengan NULL sebagai “tidak ada nilai yang dijumlah” — bukan “nilai yang dijumlahkan nol”. Akibat praktisnya nyata: kolom laporan yang diharapkan angka jadi kosong, dan aplikasi yang mengira pasti menerima angka bisa error di kode jauh dari sumber masalahnya. Solusinya adalah IFNULL yang kita kenal di chapter Fungsi NULL:

SELECT IFNULL(SUM(harga_lama), 0) AS total_aman
FROM stok_lama;

Output yang Dihasilkan:

+------------+
| total_aman |
+------------+
|       0.00 |
+------------+
1 row in set (0.00 sec)

Sekilas GROUP BY #

Semua contoh di atas meringkas seluruh tabel menjadi satu baris. Menambahkan GROUP BY kota membagi tabel menjadi kelompok per kota, lalu agregat dijalankan pada tiap kelompok dan hasilnya menjadi satu baris per kelompok:

SELECT kota, COUNT(*) AS transaksi, SUM(subtotal) AS omzet
FROM penjualan
GROUP BY kota
ORDER BY kota;

Output yang Dihasilkan:

+------------+-----------+-----------+
| kota       | transaksi | omzet     |
+------------+-----------+-----------+
| Bandung    |         2 | 105000.00 |
| Medan      |         2 | 268000.00 |
| Surabaya   |         2 | 512500.00 |
| Yogyakarta |         1 |  36000.00 |
+------------+-----------+-----------+
4 rows in set (0.00 sec)

Empat kota menghasilkan empat baris, masing-masing dengan jumlah transaksi dan omzet kotanya sendiri. Klausa ORDER BY kota kita pakai agar urutan kelompoknya pasti. Kombinasi GROUP BY + agregat inilah mesin sesungguhnya di balik laporan “omzet per bulan”, “rata-rata per kategori”, dan “jumlah pelanggan per kota” — dan seluruh mekanismenya, termasuk HAVING dan aturan kolom yang boleh menyertai GROUP BY, kita bahas tuntas di section Agregasi Data.

Membersihkan Database Latihan #

DROP DATABASE belajar_fungsi;

Output yang Dihasilkan:

Query OK, 3 rows affected (0.00 sec)

Kita sudah bisa meringkas tabel menjadi angka dan tahu persis bagaimana NULL dan DISTINCT memengaruhi hasilnya. Kolom-kolom hasil agregat tadi kita beri nama seperti omzet dan rata_rata — alias memang cara memberi nama pada kolom hasil, dan bab terakhir section ini mengupasnya sampai tuntas: alias dengan dan tanpa AS, alias bertanda kutip, alias tabel, urutan evaluasi klausa yang menjelaskan mengapa alias tidak bisa dipakai di WHERE, serta ekspresi aritmetika dengan prioritas operasornya.

Referensi #

• Aggregate Function Descriptions — MySQL 8.4 Reference Manual
• SELECT Statement — MySQL 8.4 Reference Manual