penggunaan GROUP BY, HAVING, dan fungsi agregat dalam SQL seperti SUM, AVG, MAX, MIN, dan COUNT

Artikel ini membahas penggunaan GROUP BY, HAVING, dan fungsi agregat dalam SQL seperti SUM, AVG, MAX, MIN, dan COUNT. GROUP BY digunakan untuk mengelompokkan data berdasarkan kolom yang sama, seperti mengelompokkan transaksi berdasarkan customer_id. Fungsi agregat menghitung total, rata-rata, nilai maksimum, minimum, atau jumlah data dalam kelompok tersebut. Contoh: menampilkan total pembayaran yang dilakukan oleh setiap customer. HAVING digunakan untuk memfilter hasil dari fungsi agregat setelah data dikelompokkan, misalnya menampilkan customer dengan total pembayaran lebih dari 200. Gabungan dari GROUP BY dan HAVING memungkinkan untuk memfilter dan mengelompokkan data secara efisien. Artikel ini juga memberikan beberapa soal latihan, seperti menghitung jumlah film dalam kategori "Horror", menampilkan film yang paling banyak disewa, dan menghitung jumlah customer dari kota atau negara yang sama.
Daftar Topik Utama

Group By, Having dan Aggregate function(sum, avg, max, min)

GROUP BY

  • mengelompokkan record yang isinya sama

  • contoh 1: group_by sederhana

      SELECT customer_id
  FROM payment
  GROUP BY customer_id;
    • perintah diatas adalah mengelompokan berdasarkan customer_id yang sama
    • jadi customer tersebut hanya akan ditampilkan 1x saja
    • pada contoh ini perintah group by mirip perintah distinct

  • contoh 2: group by dengan aggregate function

    SELECT customer_id, SUM (amount) AS total
FROM payment
GROUP BY customer_id;

  • contoh 3: 

    SELECT store_id, COUNT (customer_id)
FROM   customer
GROUP BY store_id

  • perintah diatas adalah menampilkan store_id dan jumlah customer yang dimiliki setiap store tersebut

HAVING

  • setelah dikelompokkan dan ditotal nilai tertentu dengan aggregate function
    hasil totalnya dapat difilter,tapi untuk memfilter hasil aggregate function tidak dengan perintah where
    tapi dengan perintah HAVING.

    SELECT customer_id, SUM (amount) AS total
FROM payment
GROUP BY customer_id
HAVING SUM (amount) > 200;

Gabungan Group by, Having dan aggregate functin

  • misalnnya dari tabel payment kita ingin menampilkan customer_id dan jumlah yang dibayar (amount) tapi dengan syarat customer_id antara 100 - 200 dan nilai totalnya harus lebih besar 100

    SELECT customer_id, SUM (amount) AS total
FROM payment
WHERE customer_ID >= 100 AND customer_id <=200
GROUP BY customer_id
HAVING sum(AMOUNT) > 100
ORDER BY sum(AMOUNT) DESC ;

  • pada perintah diatas, id dari customer di kelompokkan sedang nilai amountnya di total

  • aggregate function yang dapat digunakan SUM,AVG,MAX,MIN,COUNT

    • SUM = total Nilai, AVG = Rata-rata, MAX = Tertinggi, Min = Terendah, COUNT = jumlah data

  • perhatikan cara penulisan perintah WHERE harus ditulis sebelum Group by,

  • Sedangkan perintah Having harus ditulis setelah Group By, karena secara logika

  • hasil setelah dikelompokkan baru di filter lagi

Soal

  1. Tampilkan dari tabel payment, kolom staff_id,berapa kali mereka melayani customer (payment_id) lakukan dengan perintah group by
  2. Tampilkan dari tabel customer, kolom store_id dan jumlah customer yang dimiliki dengan syarat jumlah customernya yang melebihi 300

  3. dari tabel customer customer memiliki address dan address memiliki city
    diminta, tampilkan nama kota dan jumlah customer yang memiliki kota yang sama contoh sbb:
    contoh dibawah berarti customer dari London ada 2

    city count
    London 2
    Aurora 2
    Sasebo 2

  4. soal sama dengan soal ke 3 hanya diminta tampilkan customer dari country yang sama

    country count
    Russian Federation 28
    Greece 2
    Latvia 2
    United States 36
    Spain 5

  5. hitung ada berapa film yang memiliki kategori "Horror", kalau anda perhatikan pada sql dibawah ini dari praktek sebelumnya akan ditampilkan semua film dengan category Horror, tapi pada soal ini hanya diminta berapa jumlah film yang memiliki kategory horror.

    contoh hasilnya

    name jumlah_film
    Horror 56

  6. coba tampilkan nama film dan jumlah sewa (perhatikan table film, inventory dan rental)
    jumlah banyak disewa ada di kolom inventory_id di tabel rental

  7. tampilkan 10 film yang paling banyak disewa sepanjang masa

  8. tampilkan judul film category "Horror" yang paling banyak disewa

Jawaban

  • soal 1

    SELECT staff_id, COUNT (staff_id)
FROM   payment
GROUP BY staff_id;

  • soal 2

    SELECT store_id,  COUNT (customer_id)
FROM   customer
GROUP BY store_id;
HAVING COUNT (customer_id) > 300;

  • soal 3

    SELECT c.city, count(cu.customer_id)
FROM   customer cu
INNER JOIN address a ON cu.address_id = a.address_id
INNER JOIN city c ON c.city_id = a.city_id
GROUP BY c.city_id
HAVING COUNT (cu.customer_id) > 1;

  • soal 4

    SELECT co.country, count(cu.customer_id)
FROM   customer cu
INNER JOIN address a ON cu.address_id = a.address_id
INNER JOIN city c   ON c.city_id = a.city_id
INNER JOIN country co ON c.country_id = co.country_id
GROUP BY co.country_id
HAVING COUNT (cu.customer_id) > 1;

  • soal 5

    SELECT c.name, COUNT (f.film_id) as jumlah_film
FROM film f
INNER JOIN film_category fc ON f.film_id = fc.film_id
INNER JOIN category c ON fc.category_id = c.category_id
GROUP BY c.name
HAVING c.name = 'Horror';

  • soal 6

    SELECT f.title, COUNT(r.rental_id) as jumlah_sewa
FROM film f
INNER JOIN inventory i ON f.film_id = i.film_id
INNER JOIN rental r ON i.inventory_id = r.inventory_id
GROUP BY f.title;

  • soal 7

    SELECT f.title, COUNT(r.rental_id) as jumlah_sewa
FROM film f
INNER JOIN inventory i ON f.film_id = i.film_id
INNER JOIN rental r ON i.inventory_id = r.inventory_id
GROUP BY f.title
ORDER BY jumlah_sewa DESC
LIMIT 10;

  • soal 8

    SELECT f.title, count (r.rental_id) as total_sewa
FROM rental r
INNER JOIN inventory i ON r.inventory_id = i.inventory_id
INNER JOIN film f ON i.film_id = f.film_id
INNER JOIN film_category fc ON fc.film_id = f.film_id
INNER JOIN category c ON fc.category_id = c.category_id
WHERE c.name = 'Horror'
GROUP BY f.title
ORDER BY total_sewa DESC;