Bab 11: Aggregation & CASE WHEN

Fungsi Agregasi Dasar: SUM, COUNT, AVG

Sama seperti pada Spreadsheet yang memiliki fungsi SUM, COUNT, dan AVERAGE untuk meringkas data, SQL juga memiliki fungsi agregasi serupa yang bekerja langsung di dalam sebuah query untuk meringkas banyak baris data menjadi satu nilai ringkasan. Fungsi-fungsi ini disebut fungsi agregasi karena mengubah kumpulan baris menjadi satu angka tunggal yang mewakili keseluruhan kumpulan tersebut.

SUM digunakan untuk menjumlahkan seluruh nilai pada sebuah kolom numerik. Sebagai ilustrasi, jika kamu ingin mengetahui total keseluruhan nilai transaksi dari tabel pesanan milik Belanjain, querynya ditulis sebagai SELECT SUM(nilai_transaksi) FROM pesanan;. Query ini akan menjumlahkan seluruh nilai pada kolom nilai_transaksi dari semua baris yang ada di tabel tersebut, menghasilkan satu angka tunggal sebagai hasilnya, bukan menampilkan seluruh baris data seperti pada query SELECT biasa yang sudah dipelajari di bab sebelumnya.

COUNT digunakan untuk menghitung berapa banyak baris yang ada, dan memiliki dua variasi penulisan yang perlu dipahami perbedaannya. Penulisan COUNT(*) akan menghitung seluruh baris tanpa terkecuali, termasuk baris yang memiliki nilai kosong pada kolom tertentu, sementara penulisan COUNT(nama_kolom) hanya akan menghitung baris yang memiliki nilai tidak kosong pada kolom yang disebutkan tersebut. Sebagai ilustrasi, jika kamu ingin mengetahui berapa total baris pesanan yang ada, querynya ditulis SELECT COUNT(*) FROM pesanan;, namun jika kamu ingin mengetahui berapa banyak pesanan yang memiliki data metode pembayaran terisi (tidak kosong), querynya ditulis SELECT COUNT(metode_pembayaran) FROM pesanan;, yang hasilnya bisa jadi lebih kecil dibanding hasil COUNT(*) jika ada baris dengan kolom metode pembayaran yang kosong.

AVG digunakan untuk menghitung rata-rata dari seluruh nilai pada sebuah kolom numerik. Sebagai ilustrasi, untuk mengetahui rata-rata nilai transaksi dari seluruh pesanan Belanjain, querynya ditulis sebagai SELECT AVG(nilai_transaksi) FROM pesanan;. Perlu diperhatikan bahwa AVG secara otomatis mengabaikan baris dengan nilai kosong pada kolom yang dihitung, hanya membagi total nilai dengan jumlah baris yang memiliki nilai terisi, bukan dengan seluruh baris yang ada di tabel, sebuah perilaku yang penting dipahami agar tidak salah menginterpretasikan hasilnya.

Mengelompokkan Data dengan GROUP BY

Fungsi agregasi seperti SUM, COUNT, dan AVG yang sudah dibahas di atas menghasilkan satu angka tunggal untuk keseluruhan tabel. Namun kebutuhan analisis yang sebenarnya sering kali lebih spesifik, misalnya bukan hanya ingin tahu total keseluruhan penjualan, melainkan total penjualan per masing-masing kategori produk. Di sinilah GROUP BY berperan, memungkinkan fungsi agregasi dihitung secara terpisah untuk setiap kelompok data, bukan untuk keseluruhan tabel sekaligus.

Struktur penulisannya adalah SELECT nama_kolom, FUNGSI_AGREGASI(kolom_lain) FROM nama_tabel GROUP BY nama_kolom;. Sebagai ilustrasi, jika kamu ingin mengetahui total nilai transaksi untuk setiap kategori produk pada tabel pesanan yang memiliki kolom kategori, querynya ditulis sebagai SELECT kategori, SUM(nilai_transaksi) FROM pesanan GROUP BY kategori;. Query ini akan mengelompokkan seluruh baris berdasarkan nilai unik pada kolom kategori, kemudian menghitung SUM dari kolom nilai_transaksi secara terpisah untuk masing-masing kelompok tersebut, menghasilkan satu baris hasil untuk setiap kategori yang ada.

Satu aturan penting yang wajib dipahami ketika menggunakan GROUP BY adalah, setiap kolom yang dituliskan pada bagian SELECT tanpa dibungkus fungsi agregasi, harus ikut dituliskan juga pada bagian GROUP BY. Sebagai ilustrasi kesalahan yang sering terjadi pada pemula, query seperti SELECT kategori, kota, SUM(nilai_transaksi) FROM pesanan GROUP BY kategori; akan menghasilkan error atau perilaku yang tidak terduga, karena kolom kota dituliskan di SELECT tanpa fungsi agregasi, namun tidak ikut dituliskan di GROUP BY. Query yang benar harus menyertakan kedua kolom tersebut di GROUP BY, seperti SELECT kategori, kota, SUM(nilai_transaksi) FROM pesanan GROUP BY kategori, kota;, yang akan mengelompokkan data berdasarkan kombinasi kategori dan kota sekaligus, menghasilkan baris terpisah untuk setiap kombinasi unik dari kedua kolom tersebut.

GROUP BY pada SQL pada dasarnya memiliki konsep yang serupa dengan area Rows pada Pivot Table yang sudah dipelajari di Bab 8, di mana keduanya sama-sama berfungsi mengelompokkan data berdasarkan nilai unik pada satu atau lebih kolom, sebelum menghitung ringkasan untuk masing-masing kelompok tersebut.

Menyaring Hasil Kelompok dengan HAVING

Setelah data dikelompokkan menggunakan GROUP BY, terkadang kamu hanya ingin menampilkan kelompok-kelompok tertentu saja yang memenuhi kriteria berdasarkan hasil agregasinya, bukan seluruh kelompok yang ada. Di sinilah HAVING berperan, berfungsi menyaring hasil setelah proses pengelompokan dan agregasi selesai dilakukan.

Sebagai ilustrasi, jika kamu ingin menampilkan hanya kategori produk yang total nilai transaksinya di atas 50 juta, querynya ditulis sebagai SELECT kategori, SUM(nilai_transaksi) FROM pesanan GROUP BY kategori HAVING SUM(nilai_transaksi) > 50000000;. Perhatikan bahwa kondisi pada HAVING merujuk pada hasil fungsi agregasi (SUM(nilai_transaksi)), bukan pada kolom mentah seperti pada WHERE.

Perbedaan mendasar antara WHERE dan HAVING sering menjadi sumber kebingungan bagi pemula, karena keduanya sama-sama berfungsi menyaring data, namun bekerja pada tahap yang berbeda. WHERE menyaring baris-baris mentah sebelum proses pengelompokan dan agregasi dilakukan, sementara HAVING menyaring hasil kelompok setelah proses agregasi selesai dihitung. Konsekuensi dari perbedaan ini adalah, WHERE tidak bisa dipakai untuk menyaring berdasarkan hasil fungsi agregasi seperti SUM atau COUNT, karena pada saat WHERE diproses, agregasi tersebut memang belum dihitung sama sekali. Sebagai ilustrasi, query SELECT kategori, SUM(nilai_transaksi) FROM pesanan WHERE SUM(nilai_transaksi) > 50000000 GROUP BY kategori; akan menghasilkan error, karena mencoba memakai SUM pada bagian WHERE yang seharusnya hanya untuk HAVING.

WHERE dan HAVING bisa dipakai bersamaan dalam satu query yang sama, masing-masing untuk kebutuhan yang berbeda. Sebagai ilustrasi, jika kamu ingin menampilkan total nilai transaksi per kategori, namun hanya untuk transaksi yang terjadi di kota Jakarta, dan hanya menampilkan kategori yang totalnya di atas 50 juta, querynya menjadi SELECT kategori, SUM(nilai_transaksi) FROM pesanan WHERE kota = 'Jakarta' GROUP BY kategori HAVING SUM(nilai_transaksi) > 50000000;. Perhatikan bagaimana WHERE menyaring baris mentah terlebih dahulu (hanya kota Jakarta), baru kemudian data yang tersisa dikelompokkan berdasarkan kategori, dan terakhir hasil kelompok tersebut disaring lagi berdasarkan HAVING.

Urutan Eksekusi Query: Kenapa Berbeda dari Urutan Penulisan

Salah satu pemahaman penting yang membedakan pengguna SQL pemula dengan yang lebih berpengalaman adalah menyadari bahwa urutan penulisan sebuah query SQL (SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT) ternyata tidak sama dengan urutan pemrosesan atau eksekusi sebenarnya oleh database. Memahami urutan eksekusi yang sebenarnya ini penting untuk memahami kenapa aturan-aturan seperti "HAVING tidak bisa dipakai tanpa GROUP BY" atau "WHERE tidak bisa memakai fungsi agregasi" itu berlaku.

Urutan eksekusi sebenarnya secara garis besar adalah sebagai berikut. Pertama, FROM diproses terlebih dahulu untuk menentukan tabel mana yang menjadi sumber data. Kedua, WHERE diproses untuk menyaring baris-baris mentah dari tabel tersebut, sebelum proses pengelompokan apa pun terjadi. Ketiga, GROUP BY diproses untuk mengelompokkan baris-baris yang sudah disaring tersebut berdasarkan kolom yang ditentukan. Keempat, fungsi agregasi seperti SUM, COUNT, dan AVG dihitung untuk masing-masing kelompok yang sudah terbentuk. Kelima, HAVING diproses untuk menyaring hasil kelompok berdasarkan hasil agregasi yang sudah dihitung. Keenam, SELECT baru diproses untuk menentukan kolom apa saja yang ditampilkan sebagai hasil akhir. Ketujuh, ORDER BY diproses untuk mengurutkan hasil akhir tersebut. Kedelapan atau terakhir, LIMIT diproses untuk membatasi jumlah baris yang ditampilkan dari hasil yang sudah diurutkan.

Memahami urutan ini menjelaskan kenapa WHERE tidak bisa memakai hasil fungsi agregasi, karena pada saat WHERE diproses (urutan kedua), fungsi agregasi belum dihitung sama sekali (baru dihitung di urutan keempat). Ini juga menjelaskan kenapa HAVING justru bisa memakai hasil fungsi agregasi, karena HAVING diproses setelah agregasi selesai dihitung. Pemahaman urutan eksekusi ini akan sangat membantu ketika kamu menemukan error yang tidak terduga dalam menulis query, karena kamu bisa menelusuri di tahap mana sebenarnya kesalahan logika tersebut terjadi, dibanding hanya menghafal aturan tanpa memahami alasan di baliknya.

CASE WHEN: Logika Kondisional dalam SQL

Jika pada Spreadsheet kamu sudah mempelajari fungsi IF untuk menampilkan hasil berbeda berdasarkan kondisi tertentu, dalam SQL, kemampuan serupa dicapai menggunakan CASE WHEN. Struktur dasarnya adalah:

CASE
  WHEN kondisi_1 THEN hasil_1
  WHEN kondisi_2 THEN hasil_2
  ELSE hasil_lainnya
END

CASE WHEN akan memeriksa setiap kondisi WHEN secara berurutan dari atas ke bawah, dan begitu menemukan kondisi yang terpenuhi, akan langsung menampilkan hasil (THEN) yang sesuai tanpa memeriksa kondisi WHEN berikutnya, meskipun kondisi berikutnya tersebut sebenarnya juga terpenuhi. Jika tidak ada satu pun kondisi WHEN yang terpenuhi, hasil yang ditampilkan adalah nilai pada bagian ELSE, yang sifatnya opsional namun sangat disarankan untuk selalu dituliskan agar tidak menghasilkan nilai kosong yang membingungkan ketika tidak ada kondisi yang cocok.

Sebagai ilustrasi dalam konteks Belanjain, bayangkan kamu ingin mengelompokkan setiap pesanan ke dalam kategori "Kecil", "Sedang", atau "Besar" berdasarkan nilai transaksinya, mirip dengan contoh nested IF yang sudah dibahas di Bab 6 pada Spreadsheet. Query lengkapnya menjadi:

SELECT 
  id_pesanan,
  nilai_transaksi,
  CASE
    WHEN nilai_transaksi < 100000 THEN 'Kecil'
    WHEN nilai_transaksi <= 500000 THEN 'Sedang'
    ELSE 'Besar'
  END AS kategori_transaksi
FROM pesanan;

Perhatikan penggunaan kata kunci AS setelah END, yang berfungsi memberikan nama alias pada kolom hasil CASE WHEN tersebut, dalam contoh ini diberi nama kategori_transaksi, sehingga hasil kolom baru ini memiliki nama yang jelas ketika ditampilkan, bukan hanya nama generik yang kurang informatif. Pemberian alias dengan AS ini adalah kebiasaan yang sangat disarankan setiap kali kamu membuat kolom hasil perhitungan atau logika kondisional, agar hasil query lebih mudah dipahami.

CASE WHEN juga bisa digabungkan dengan fungsi agregasi untuk kebutuhan yang lebih kompleks, sebuah teknik yang sangat berguna dalam praktik nyata. Sebagai ilustrasi, jika kamu ingin menghitung revenue per cabang Belanjain, sekaligus mengelompokkan cabang tersebut ke dalam kategori performa "Tinggi" atau "Rendah" berdasarkan hasil revenue yang dihitung, querynya menjadi:

SELECT 
  cabang,
  SUM(nilai_transaksi) AS total_revenue,
  CASE
    WHEN SUM(nilai_transaksi) > 100000000 THEN 'Performa Tinggi'
    ELSE 'Performa Rendah'
  END AS kategori_performa
FROM pesanan
GROUP BY cabang;

Perhatikan bagaimana CASE WHEN pada contoh ini memeriksa hasil dari SUM(nilai_transaksi), bukan kolom mentah, yang berarti CASE WHEN pada konteks ini diproses setelah agregasi dan pengelompokan selesai dihitung, konsisten dengan urutan eksekusi query yang sudah dibahas sebelumnya.

Age dan Price Bucketing dengan CASE WHEN

Salah satu penerapan CASE WHEN yang sangat umum dalam analisis data adalah untuk melakukan bucketing, yaitu mengelompokkan data numerik yang bersifat kontinu (bisa bernilai apa saja dalam sebuah rentang) menjadi beberapa kategori atau kelompok yang lebih mudah dianalisis, mirip dengan contoh kategori "Kecil, Sedang, Besar" yang sudah dibahas di atas, namun bisa diterapkan pada berbagai konteks lain.

Sebagai ilustrasi price bucketing, bayangkan Belanjain ingin mengelompokkan seluruh produk yang dijual ke dalam beberapa rentang harga untuk keperluan analisis segmentasi pasar, misalnya "Ekonomis" untuk harga di bawah 100000, "Menengah" untuk harga 100000 hingga 500000, dan "Premium" untuk harga di atas 500000. Query untuk kebutuhan ini akan sangat mirip dengan contoh kategori transaksi sebelumnya, hanya berbeda pada kolom dan batas nilai yang diperiksa.

Sebagai ilustrasi age bucketing, jika Belanjain memiliki data umur pelanggan dan ingin mengelompokkannya ke dalam kategori generasi untuk keperluan analisis demografi, misalnya "Gen Z" untuk umur di bawah 27 tahun, "Milenial" untuk umur 27 hingga 42 tahun, dan "Gen X ke atas" untuk umur di atas 42 tahun, pendekatan CASE WHEN yang sama juga bisa diterapkan. Bucketing seperti ini sangat berguna karena data numerik mentah seperti umur atau harga yang sangat detail dan bervariasi, sering kali lebih mudah dipahami dan dianalisis ketika sudah dikelompokkan menjadi beberapa kategori yang lebih sedikit dan bermakna, sebuah teknik yang akan kembali dibahas lebih mendalam pada Bagian V Handbook ini mengenai statistik dan EDA.