Cara Menggunakan GROUP BY dan HAVING di SQL

Kami akan menggunakan Basis Data Perusahaan Unicorn, yang tersedia di DataLab, notebook data bertenaga AI dari DataCamp. Perusahaan-perusahaan ini disebut Unicorn karena merupakan perusahaan rintisan dengan valuasi lebih dari satu miliar dolar. Untuk kesederhanaan, kami akan berfokus pada tiga tabel: companies, sales, dan product_emissions.  Untuk menjalankan semua contoh kode dalam tutorial ini sendiri, Anda dapat membuat workbook DataLab secara gratis dengan basis data dan semua contoh kode yang sudah dimuat.

Menggunakan SQL GROUP BY

GROUP BY adalah perintah SQL yang umum digunakan untuk mengagregasikan data guna mendapatkan wawasan. Ada tiga fase saat Anda mengelompokkan data:

• Pemisahan (Split): dataset dipecah menjadi potongan baris berdasarkan nilai variabel yang kita pilih untuk agregasi
• Penerapan (Apply): Menghitung fungsi agregat, seperti rata-rata, minimum, dan maksimum, yang menghasilkan satu nilai
• Penggabungan (Combine): Semua keluaran hasil ini digabungkan dalam satu tabel. Dengan cara ini, kita akan memiliki satu nilai untuk setiap modalitas variabel yang diminati.

Contoh SQL GROUP BY 1

Kita bisa mulai dengan menunjukkan contoh sederhana GROUP BY. Misalkan kita ingin menemukan sepuluh negara teratas dengan jumlah perusahaan Unicorn terbanyak. 

SELECT * 
FROM companies

Akan lebih baik juga jika hasilnya diurutkan menurun berdasarkan jumlah perusahaan

SELECT country, COUNT(*) AS n_companies
FROM companies
GROUP BY country
ORDER BY n_companies DESC
LIMIT 10

Berikut hasilnya. Anda mungkin tidak terkejut menemukan AS, Tiongkok, dan India dalam peringkat tersebut. Mari jelaskan pertimbangan di balik kueri ini: 

• Pertama, perhatikan bahwa kami menggunakan COUNT(*) untuk menghitung baris untuk setiap grup, yang sesuai dengan negara. Selain itu, kami juga menggunakan alias SQL untuk mengganti nama kolom menjadi nama yang lebih mudah dipahami. Ini dimungkinkan dengan menggunakan kata kunci AS, diikuti nama baru. COUNT dibahas lebih mendalam dalam tutorial COUNT() SQL FUNCTION.

• Field dipilih dari tabel companies, di mana setiap barisnya sesuai dengan satu perusahaan Unicorn. 

• Setelah itu, kita perlu menyebutkan nama kolom setelah GROUP BY untuk mengagregasikan data berdasarkan negara. 

• ORDER BY diperlukan untuk menampilkan negara dalam urutan yang benar, dari jumlah perusahaan tertinggi ke terendah. 

• Kami membatasi hasil hingga 10 menggunakan LIMIT, yang diikuti oleh jumlah baris yang Anda inginkan di hasil.

Contoh SQL GROUP BY 2

Sekarang, kita akan menganalisis tabel penjualan. Untuk setiap nomor pesanan, kita memiliki tipe klien, lini produk, kuantitas, harga per unit, total, dan seterusnya.

Kali ini, kita tertarik untuk mengetahui harga rata-rata per unit, total jumlah pesanan, dan total keuntungan untuk setiap lini produk:

SELECT 
    product_line,
    AVG(unit_price) AS avg_price,
    SUM(quantity) AS tot_pieces,
    SUM(total) AS total_gain
FROM sales
GROUP BY product_line
ORDER BY total_gain DESC

• Alih-alih menghitung jumlah baris, kita menggunakan fungsi AVG() untuk memperoleh harga rata-rata dan fungsi SUM() untuk menghitung total jumlah pesanan serta total keuntungan untuk setiap lini produk. 

• Seperti sebelumnya, kita menyebutkan kolom yang awalnya membagi dataset menjadi potongan. Lalu fungsi agregasi memungkinkan kita memperoleh satu baris untuk setiap modalitas lini produk. 

• Kali ini, ORDER BY bersifat opsional. Itu disertakan untuk menyoroti bagaimana total keuntungan yang lebih tinggi tidak selalu sebanding dengan harga rata-rata atau jumlah unit yang lebih tinggi. 

Keterbatasan WHERE

Mari ambil lagi contoh sebelumnya. Sekarang, kita ingin menambahkan kondisi pada kueri: kita hanya ingin memfilter untuk total jumlah pesanan lebih dari 40.000. Mari coba klausa WHERE:

SELECT 
    product_line,
    AVG(unit_price) AS avg_price,
    SUM(quantity) AS tot_pieces,
    SUM(total) AS total_gain
FROM sales
WHERE SUM(total) > 40000
GROUP BY product_line
ORDER BY total_gain DESC

Kueri ini akan menghasilkan error berikut:

Error ini terjadi karena tidak memungkinkan untuk menempatkan fungsi agregat dalam klausa WHERE. Kita memerlukan perintah baru untuk menyelesaikan masalah ini.

Menggunakan SQL HAVING

Seperti WHERE, klausa HAVING memfilter baris suatu tabel. Jika WHERE mencoba memfilter seluruh tabel, HAVING memfilter baris di dalam masing-masing grup yang didefinisikan oleh GROUP BY

Contoh SQL HAVING 1

Berikut contoh sebelumnya lagi, mengganti kata WHERE dengan HAVING.

SELECT 
    product_line,
    AVG(unit_price) AS avg_price,
    SUM(quantity) AS tot_pieces,
    SUM(total) AS total_gain
FROM sales
GROUP BY product_line
HAVING SUM(total) > 40000
ORDER BY total_gain DESC

Kali ini akan menghasilkan tiga baris. Lini produk lainnya tidak memenuhi kriteria, sehingga kita beralih dari enam hasil menjadi tiga. 

Apa lagi yang Anda perhatikan dari kueri tersebut? Kita tidak meneruskan alias kolom ke HAVING, melainkan agregasi dari field aslinya. Bertanya-tanya mengapa? Anda akan menemukan jawabannya di contoh berikutnya.

Contoh SQL HAVING 2

Sebagai contoh terakhir, kita akan menggunakan tabel bernama product_emissions, yang berisi emisi dari produk yang disediakan oleh perusahaan.

Kali ini, kita tertarik menampilkan rata-rata jejak karbon produk (pcf) untuk setiap perusahaan yang termasuk dalam kelompok industri “Technology Hardware & Equipment.” Selain itu, akan bermanfaat untuk melihat jumlah produk per perusahaan guna memahami apakah ada hubungan antara jumlah produk dan jejak karbon. Kita juga kembali menggunakan HAVING untuk mengambil perusahaan dengan rata-rata jejak karbon di atas 100.

SELECT pe.company, count(product_name) AS n_products, avg(carbon_footprint_pcf) AS avg_carbon_footprint_pcf
FROM product_emissions AS pe
WHERE industry_group = 'Technology Hardware & Equipment'
GROUP BY pe.company, industry_group
having avg_carbon_footprint_pcf>100
ORDER BY n_products

Muncul error setelah mencoba menggunakan alias. Untuk klausa HAVING, nama kolom baru belum ada, sehingga tidak dapat memfilter kueri. Mari perbaiki permintaannya:

SELECT pe.company, count(product_name) AS n_products, avg(carbon_footprint_pcf) AS avg_carbon_footprint_pcf
FROM product_emissions AS pe
WHERE industry_group = 'Technology Hardware & Equipment'
GROUP BY pe.company, industry_group
having avg(carbon_footprint_pcf)>100
ORDER BY n_products

Kali ini, kondisinya berhasil, dan kita dapat menampilkan hasil dari tabel. Kita baru belajar bahwa alias kolom tidak dapat digunakan di HAVING karena kondisi ini diterapkan sebelum SELECT. Karena alasan ini, ia tidak dapat mengenali field dari nama yang baru.

Urutan Eksekusi SQL

Ini adalah urutan perintah saat menulis kueri:

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

Tapi ada pertanyaan yang perlu Anda ajukan. Dalam urutan apa perintah SQL dieksekusi? Sebagai manusia, kita sering menganggap komputer membaca dan menafsirkan SQL dari atas ke bawah. Namun kenyataannya berbeda dari yang terlihat. Inilah urutan eksekusi yang benar:

FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY
LIMIT

Jadi, pemroses kueri tidak memulai dari SELECT, melainkan mulai dari memilih tabel mana yang akan disertakan, dan SELECT dieksekusi setelah HAVING. Ini menjelaskan mengapa HAVING tidak mengizinkan penggunaan ALIAS, sementara ORDER BY tidak mengalami masalah dengannya. Selain itu, urutan eksekusi ini memperjelas alasan mengapa HAVING digunakan bersama GROUP BY untuk menerapkan kondisi pada data teragregasi, sedangkan WHERE tidak dapat. 

Saya menyarankan membaca tutorial kami, SQL Order of Execution: Understanding How Queries Run, yang memberikan penjelasan yang baik, jika Anda ingin belajar lebih lanjut.

Naik ke Tingkat Berikutnya

Setelah membaca tutorial ini, Anda seharusnya memiliki gambaran jelas tentang perbedaan antara GROUP BY dan HAVING. Anda dapat berlatih di DataLab untuk menguasai konsep-konsep ini.

Jika Anda ingin naik ke tingkat berikutnya dalam jalur pembelajaran SQL, Anda dapat mengikuti kursus Intermediate SQL kami. Jika Anda masih perlu memperkuat dasar-dasar SQL, Anda dapat kembali ke kursus Introduction to SQL untuk mempelajari fundamental bahasanya.