Fungsi Agregat

Membaca data baris demi baris tidak membantu ketika pertanyaannya berbentuk “berapa banyak”, “berapa total”, atau “berapa rata-rata”. Fungsi agregat menjawab pertanyaan seperti itu dengan meringkas banyak baris menjadi satu nilai. Kita akan membedah lima fungsi agregat bawaan SQLite — COUNT, SUM, AVG, MIN, MAX — dan membongkar satu per satu perilakunya terhadap NULL, karena di sanalah letak mayoritas kejutan hasil yang “tidak masuk akal”.

Data Contoh #

Chapter ini memakai dua tabel dari domain toko. Kolom kota dan stok masing-masing memuat satu NULL yang akan sering kita pakai:

CREATE TABLE pelanggan (
  id INTEGER PRIMARY KEY,
  nama TEXT NOT NULL,
  kota TEXT
);
CREATE TABLE produk (
  id INTEGER PRIMARY KEY,
  nama TEXT NOT NULL,
  harga INTEGER NOT NULL,
  stok INTEGER
);

INSERT INTO pelanggan (id, nama, kota) VALUES
  (1, 'Budi', 'Jakarta'), (2, 'Siti', 'Bandung'), (3, 'Ani', NULL),
  (4, 'Rina', 'Surabaya'), (5, 'Dewi', 'Jakarta');
INSERT INTO produk (id, nama, harga, stok) VALUES
  (1, 'Kopi Gayo', 45000, 20), (2, 'Teh Melati', 18000, 35),
  (3, 'Gula Aren', 25000, 15), (4, 'Sirup Vanila', 25000, NULL),
  (5, 'Kopi Toraja', 52000, 10), (6, 'Teh Hijau', 22000, 0),
  (7, 'Cangkir Keramik', 35000, 8);

COUNT: Menghitung Baris #

COUNT(*) menghitung semua baris, termasuk baris yang memuat NULL. COUNT(kolom) hanya menghitung baris yang kolomnya tidak NULL — perbedaan inilah sumber kejutan pertama:

SELECT COUNT(*) AS semua_baris, COUNT(kota) AS kota_terisi
FROM pelanggan;

Output yang Dihasilkan:

5|4

Lima pelanggan ada, tetapi hanya empat yang kotanya terisi — Ani tidak dihitung oleh COUNT(kota). Pola yang sama muncul pada stok: produk ada tujuh, stok yang tercatat hanya enam karena Sirup Vanila NULL:

SELECT COUNT(*) AS semua_baris, COUNT(stok) AS stok_terisi
FROM produk;

Output yang Dihasilkan:

7|6

Varian terakhir, COUNT(DISTINCT kota), menghitung nilai unik saja. Dari lima pelanggan hanya ada tiga kota berbeda:

SELECT COUNT(DISTINCT kota) AS kota_unik
FROM pelanggan;

Output yang Dihasilkan:

3

SUM dan AVG: NULL Diabaikan, Bukan Nol #

SUM menjumlahkan dan AVG merata-ratakan nilai non-NULL; baris bernilai NULL dilewati seolah tidak ada:

SELECT SUM(stok) AS total_stok, AVG(stok) AS rata_stok
FROM produk;

Output yang Dihasilkan:

88|14.6666666666667

Total stok adalah 88, dijumlahkan dari enam nilai yang tercatat. NULL diperlakukan seolah barisnya tidak ada — bukan seolah nilainya nol — dan perbedaan ini paling terlihat pada AVG: rata-rata dihitung dari enam nilai (88 ÷ 6), bukan tujuh. Perhatikan juga bahwa AVG selalu menghasilkan nilai REAL meskipun semua masukan bilangan bulat.

Untuk tabel tanpa satu pun baris yang memenuhi syarat, perilakunya berbeda: COUNT(*) menghasilkan 0, tetapi SUM, AVG, MIN, dan MAX menghasilkan NULL:

SELECT COUNT(*) AS jumlah, SUM(harga) AS total, AVG(harga) AS rata, MIN(harga) AS termurah
FROM produk
WHERE id > 100;

Output yang Dihasilkan:

0|||

SQLite menyediakan fungsi non-standar total() yang selalu menghasilkan REAL dan mengembalikan 0.0 alih-alih NULL pada himpunan kosong — berguna saat angka nol lebih bermakna daripada kosong:

SELECT total(stok) AS total_stok FROM produk;

Output yang Dihasilkan:

88.0

MIN dan MAX: Bekerja Juga untuk Teks #

MIN dan MAX mencari nilai terkecil dan terbesar, dan keduanya mengabaikan NULL. Selain angka, keduanya juga berlaku untuk teks dengan urutan kamus (Unicode ordering):

SELECT MIN(nama) AS nama_awal, MAX(nama) AS nama_akhir
FROM produk;

Output yang Dihasilkan:

Cangkir Keramik|Teh Melati

Pada kolom stok, nilai terkecil adalah 0 — Teh Hijau memang habis — dan terbesar 35:

SELECT MIN(stok) AS stok_terkecil, MAX(stok) AS stok_terbesar
FROM produk;

Output yang Dihasilkan:

0|35

Fungsi ROUND sering dipasangkan dengan AVG agar hasilnya enak dibaca; rata-rata harga produk kita adalah 31714.2857142857 sebelum dibulatkan:

SELECT AVG(harga) AS rata_rata, ROUND(AVG(harga), 2) AS dibulatkan
FROM produk;

Output yang Dihasilkan:

31714.2857142857|31714.29

Jebakan SUM: Bilangan Bulat yang Meluap #

SUM menghasilkan INTEGER hanya jika semua masukannya bilangan bulat, dan REAL begitu ada satu saja masukan pecahan. Dengan fungsi typeof() kita bisa melihat keduanya:

SELECT SUM(harga) AS total, typeof(SUM(harga)) AS tipe
FROM produk;

Output yang Dihasilkan:

222000|integer

Konsekuensinya serius ketika angkanya sangat besar. Jika semua masukan bilangan bulat dan penjumlahannya melampaui kapasitas 64 bit, SQLite membatalkan query dengan galat:

sqlite3 toko.db "SELECT SUM(x) FROM (SELECT 9223372036854775807 AS x UNION ALL SELECT 1);"

Output yang Dihasilkan:

Error: stepping, integer overflow

Sebaliknya, satu masukan pecahan saja membuat SUM langsung bekerja sebagai REAL — tanpa galat, tetapi dengan presisi pecahan yang terbatas. Nilai terbesar 64 bit di bawah ini kehilangan digit-detailnya:

SELECT SUM(x) AS total, typeof(SUM(x)) AS tipe
FROM (SELECT 9223372036854775807 AS x UNION ALL SELECT 0.0);

Output yang Dihasilkan:

9.22337203685478e+18|real

Semua fungsi pada chapter ini meringkas seluruh tabel menjadi satu baris. Pertanyaan berikutnya biasanya lebih spesifik: “berapa total per pelanggan?” — dan untuk itu kita butuh GROUP BY, yang dibahas pada Chapter Mengelompokan Hasil dengan GROUP BY dan HAVING.