Query SQL Lanjutan: GROUP BY, HAVING, dan Subquery

Memeriksa Data Sebelum Membuat Laporan#

Coba jalankan:

1
SELECT
2
    p.id_pesanan,
3
    pl.nama_pelanggan,
4
    pr.nama_produk,
5
    dp.jumlah,
6
    dp.harga_satuan,
7
    dp.jumlah * dp.harga_satuan AS subtotal
8
FROM pesanan p
9
INNER JOIN pelanggan pl
10
    ON p.id_pelanggan = pl.id_pelanggan
11
INNER JOIN detail_pesanan dp
12
    ON p.id_pesanan = dp.id_pesanan
13
INNER JOIN produk pr
14
    ON dp.id_produk = pr.id_produk
15
ORDER BY p.id_pesanan, dp.id_detail;

Query tersebut menggabungkan empat tabel untuk menampilkan detail pembelian.

Kita akan menggunakan data yang sama untuk berbagai latihan analisis.

COUNT — Menghitung Jumlah Data#

Misalnya, kita ingin mengetahui jumlah produk yang tersedia dalam tabel.

1
SELECT COUNT(*) AS jumlah_produk
2
FROM produk;

COUNT(*) menghitung jumlah baris pada tabel, termasuk baris yang memiliki nilai NULL pada kolom tertentu.

Jika tabel produk memiliki empat baris setelah latihan Bab 4, hasilnya adalah 4.

Tetapi jika kamu menambahkan produk lain, jumlahnya tentu berbeda.

Sekarang coba menghitung jumlah pelanggan:

1
SELECT COUNT(*) AS jumlah_pelanggan
2
FROM pelanggan;

Kita juga bisa menghitung jumlah pesanan:

1
SELECT COUNT(*) AS jumlah_pesanan
2
FROM pesanan;

Perhatikan perbedaan berikut:

1
SELECT
2
    COUNT(*) AS total_baris,
3
    COUNT(email) AS email_tidak_null
4
FROM pelanggan;

COUNT(*) menghitung semua baris.

Sementara COUNT(email) hanya menghitung baris dengan nilai email yang tidak NULL.

Karena pada struktur tabel pelanggan kita kolom email menggunakan NOT NULL, kedua hasil tersebut seharusnya sama.

SUM — Menjumlahkan Nilai#

Misalnya, kita ingin menghitung total stok seluruh produk.

1
SELECT SUM(stok) AS total_stok
2
FROM produk;

MySQL akan menjumlahkan nilai stok dari semua produk.

Sekarang coba menghitung total nilai seluruh detail transaksi:

1
SELECT
2
    SUM(jumlah * harga_satuan) AS total_penjualan
3
FROM detail_pesanan;

Jika hanya ada tiga baris detail transaksi dari Bab 5, hasilnya:

1
2245000.00

Perhitungannya:

1
325000 + (2 × 135000) + 1650000 = 2245000

Nilai tersebut merupakan total nominal detail pesanan yang tercatat.

Kita menyebutnya total penjualan untuk latihan ini, tetapi pada aplikasi nyata, definisi omzet perlu mempertimbangkan status pesanan, pembatalan, retur, diskon, pajak, dan biaya lainnya.

Jangan langsung menganggap semua pesanan yang tercatat sudah menjadi pendapatan final.

AVG — Menghitung Rata-Rata#

Untuk menghitung rata-rata harga produk:

1
SELECT AVG(harga) AS rata_rata_harga
2
FROM produk;

AVG() menghitung rata-rata dari nilai yang tidak NULL.

Contoh lain, rata-rata jumlah unit per baris detail pesanan:

1
SELECT AVG(jumlah) AS rata_rata_jumlah
2
FROM detail_pesanan;

Perlu diperhatikan, query tersebut menghitung rata-rata jumlah unit per baris detail, bukan rata-rata jumlah barang per pesanan.

Kedua pertanyaan tersebut berbeda dan dapat menghasilkan nilai yang berbeda.

MIN dan MAX — Nilai Terkecil dan Terbesar#

Untuk mencari harga produk termurah dan termahal:

1
SELECT
2
    MIN(harga) AS harga_termurah,
3
    MAX(harga) AS harga_termahal
4
FROM produk;

Query tersebut hanya mengembalikan nilai harga, bukan nama produknya.

Kalau ingin mengetahui produk yang memiliki harga tersebut, kita perlu query tambahan atau menggunakan subquery yang akan dipelajari nanti.

Menghitung Total Unit Terjual per Produk#

Jalankan:

1
SELECT
2
    pr.id_produk,
3
    pr.nama_produk,
4
    SUM(dp.jumlah) AS total_terjual
5
FROM detail_pesanan dp
6
INNER JOIN produk pr
7
    ON dp.id_produk = pr.id_produk
8
GROUP BY
9
    pr.id_produk,
10
    pr.nama_produk
11
ORDER BY total_terjual DESC;

Query tersebut melakukan beberapa hal:

1. Menggabungkan detail pesanan dengan produk.
2. Mengelompokkan baris berdasarkan produk.
3. Menjumlahkan jumlah pembelian pada setiap kelompok.
4. Mengurutkan hasil berdasarkan total unit terjual.

Jika menggunakan data contoh Bab 5, hasilnya:

Sekarang kita tahu bahwa Mouse Wireless menjadi produk dengan unit penjualan terbanyak dalam data latihan.

Namun, produk yang paling banyak terjual belum tentu menghasilkan nilai penjualan terbesar.

Harga produk juga berpengaruh.

Menghitung Nilai Penjualan per Produk#

Coba jalankan:

1
SELECT
2
    pr.id_produk,
3
    pr.nama_produk,
4
    SUM(dp.jumlah) AS total_unit,
5
    SUM(dp.jumlah * dp.harga_satuan) AS total_nilai
6
FROM detail_pesanan dp
7
INNER JOIN produk pr
8
    ON dp.id_produk = pr.id_produk
9
GROUP BY
10
    pr.id_produk,
11
    pr.nama_produk
12
ORDER BY total_nilai DESC;

Hasil ilustrasinya:

Menarik, bukan?

Mouse Wireless paling banyak terjual berdasarkan jumlah unit.

Tetapi Monitor 24 Inch menghasilkan nilai penjualan terbesar.

Jadi, ketika seseorang bertanya “produk terlaris”, kita perlu memastikan maksudnya:

• Paling banyak unit terjual?
• Paling tinggi nilai penjualan?
• Paling sering muncul dalam pesanan?

Definisi metrik yang berbeda dapat menghasilkan peringkat yang berbeda.

Menghitung Total Pembelian per Pelanggan#

Sekarang kita ingin mengetahui total nilai pembelian setiap pelanggan.

1
SELECT
2
    pl.id_pelanggan,
3
    pl.nama_pelanggan,
4
    SUM(dp.jumlah * dp.harga_satuan) AS total_pembelian
5
FROM pelanggan pl
6
INNER JOIN pesanan p
7
    ON pl.id_pelanggan = p.id_pelanggan
8
INNER JOIN detail_pesanan dp
9
    ON p.id_pesanan = dp.id_pesanan
10
GROUP BY
11
    pl.id_pelanggan,
12
    pl.nama_pelanggan
13
ORDER BY total_pembelian DESC;

Jika data sesuai contoh, Budi berada di atas Andi karena total nilai pembeliannya lebih besar.

Query tersebut hanya menampilkan pelanggan yang memiliki detail transaksi.

Pelanggan yang belum melakukan pembelian tidak akan muncul karena kita menggunakan INNER JOIN.

Kenapa Menggunakan COUNT(DISTINCT)?#

Karena satu pesanan dapat memiliki beberapa baris detail.

Jika kita menggunakan COUNT(p.id_pesanan) setelah JOIN dengan detail_pesanan, satu pesanan yang memiliki dua produk bisa ikut terhitung dua kali.

Dengan COUNT(DISTINCT p.id_pesanan), kita menghitung ID pesanan yang berbeda.

Apa Fungsi COALESCE?#

COALESCE() mengembalikan nilai pertama yang tidak NULL dari daftar argumennya.

Pada query ini:

1
COALESCE(SUM(dp.jumlah * dp.harga_satuan), 0)

Jika pelanggan belum mempunyai detail pembelian dan hasil SUM() adalah NULL, nilai tersebut ditampilkan sebagai 0.

Dengan begitu, pelanggan tanpa pembelian tetap muncul dengan total nol.

Menggunakan HAVING#

Jalankan:

1
SELECT
2
    pl.id_pelanggan,
3
    pl.nama_pelanggan,
4
    SUM(dp.jumlah * dp.harga_satuan) AS total_pembelian
5
FROM pelanggan pl
6
INNER JOIN pesanan p
7
    ON pl.id_pelanggan = p.id_pelanggan
8
INNER JOIN detail_pesanan dp
9
    ON p.id_pesanan = dp.id_pesanan
10
GROUP BY
11
    pl.id_pelanggan,
12
    pl.nama_pelanggan
13
HAVING SUM(dp.jumlah * dp.harga_satuan) > 500000
14
ORDER BY total_pembelian DESC;

Query tersebut hanya menampilkan pelanggan dengan total pembelian lebih dari Rp500.000.

Pada data contoh, Andi dan Budi sama-sama memenuhi kondisi.

Perbedaan WHERE dan HAVING#

Secara konseptual, prosesnya dapat dibayangkan seperti ini:

1
FROM / JOIN
2
     |
3
   WHERE
4
     |
5
  GROUP BY
6
     |
7
   HAVING
8
     |
9
   SELECT
10
     |
11
  ORDER BY
12
     |
13
   LIMIT

Urutan tersebut merupakan gambaran logis untuk memahami query, bukan urutan fisik yang selalu digunakan oleh mesin database.

MySQL dapat mengoptimalkan pelaksanaan query dengan strategi yang berbeda.

Menggabungkan WHERE dan HAVING#

Misalnya, kita hanya ingin menghitung pesanan berstatus selesai, lalu menampilkan pelanggan dengan total pembelian lebih dari Rp500.000.

1
SELECT
2
    pl.id_pelanggan,
3
    pl.nama_pelanggan,
4
    SUM(dp.jumlah * dp.harga_satuan) AS total_pembelian
5
FROM pelanggan pl
6
INNER JOIN pesanan p
7
    ON pl.id_pelanggan = p.id_pelanggan
8
INNER JOIN detail_pesanan dp
9
    ON p.id_pesanan = dp.id_pesanan
10
WHERE p.status_pesanan = 'selesai'
11
GROUP BY
12
    pl.id_pelanggan,
13
    pl.nama_pelanggan
14
HAVING SUM(dp.jumlah * dp.harga_satuan) > 500000;

WHERE memilih baris pesanan yang berstatus selesai.

Setelah itu, GROUP BY mengelompokkan data berdasarkan pelanggan.

Terakhir, HAVING memfilter kelompok berdasarkan total pembelian.

Catatan: Pada Bab 5, pesanan contoh kita menggunakan status baru. Jadi, query di atas bisa menghasilkan nol baris jika belum ada pesanan berstatus selesai.

Hal tersebut bukan berarti query salah.

Hasilnya memang mengikuti kondisi data.

Untuk mencoba dengan data contoh tanpa mengubah status pesanan, kamu dapat mengganti 'selesai' menjadi 'baru'.

Mencari Pelanggan yang Belum Pernah Memesan#

Kita bisa menggunakan NOT EXISTS.

1
SELECT
2
    pl.id_pelanggan,
3
    pl.nama_pelanggan
4
FROM pelanggan pl
5
WHERE NOT EXISTS (
6
    SELECT 1
7
    FROM pesanan p
8
    WHERE p.id_pelanggan = pl.id_pelanggan
9
);

Query tersebut menampilkan pelanggan yang tidak memiliki pesanan.

Ini merupakan alternatif dari pola LEFT JOIN ... IS NULL yang sudah kita pelajari pada Bab 5.

1. Memilih Kolom yang Tidak Sesuai dengan GROUP BY#

Perhatikan contoh:

1
SELECT
2
    id_pelanggan,
3
    status_pesanan,
4
    COUNT(*)
5
FROM pesanan
6
GROUP BY id_pelanggan;

Query tersebut bermasalah secara konsep karena satu pelanggan bisa mempunyai beberapa pesanan dengan status berbeda.

Kolom status_pesanan tidak memiliki satu nilai yang pasti untuk setiap kelompok pelanggan.

Pada MySQL dengan ONLY_FULL_GROUP_BY aktif, query seperti ini dapat ditolak jika kolom tersebut tidak bergantung secara fungsional pada kolom pengelompokan.

Jika kita ingin menghitung jumlah pesanan berdasarkan pelanggan dan status, gunakan:

1
SELECT
2
    id_pelanggan,
3
    status_pesanan,
4
    COUNT(*) AS jumlah_pesanan
5
FROM pesanan
6
GROUP BY
7
    id_pelanggan,
8
    status_pesanan;

Dengan begitu, pengelompokannya menjadi jelas.

2. Menggunakan WHERE untuk Memfilter SUM#

Contoh yang tidak tepat:

1
SELECT
2
    id_pesanan,
3
    SUM(jumlah * harga_satuan) AS total
4
FROM detail_pesanan
5
WHERE SUM(jumlah * harga_satuan) > 500000
6
GROUP BY id_pesanan;

Fungsi agregasi tidak digunakan secara langsung dalam kondisi WHERE pada tingkat query yang sama.

Gunakan HAVING:

1
SELECT
2
    id_pesanan,
3
    SUM(jumlah * harga_satuan) AS total
4
FROM detail_pesanan
5
GROUP BY id_pesanan
6
HAVING SUM(jumlah * harga_satuan) > 500000;

3. Menganggap COUNT Selalu Menghitung Data Unik#

Perhatikan:

1
SELECT COUNT(id_pesanan)
2
FROM detail_pesanan;

Query tersebut menghitung jumlah nilai id_pesanan yang tidak NULL pada baris detail.

Jika satu pesanan memiliki tiga baris detail, ID pesanan tersebut ikut dihitung tiga kali.

Untuk menghitung jumlah pesanan yang berbeda:

1
SELECT COUNT(DISTINCT id_pesanan) AS jumlah_pesanan
2
FROM detail_pesanan;

4. Tidak Memahami Hasil NULL#

Fungsi agregasi memiliki perilaku berbeda terhadap NULL.

COUNT(*) menghitung baris, sedangkan COUNT(kolom) mengabaikan nilai NULL.

SUM() dan AVG() juga mengabaikan nilai NULL dalam perhitungannya.

Jika tidak ada nilai yang dapat dijumlahkan, SUM() dapat menghasilkan NULL.

Gunakan COALESCE() ketika memang ingin menampilkan nilai pengganti, misalnya nol.

5. Menggunakan Subquery Tanpa Memahami Jumlah Hasilnya#

Subquery yang digunakan bersama operator = harus menghasilkan paling banyak satu baris.

Jika subquery dapat menghasilkan banyak nilai, pertimbangkan menggunakan IN, EXISTS, atau pendekatan JOIN sesuai kebutuhan.

Memahami bentuk hasil subquery sama pentingnya dengan memahami sintaksnya.

Latihan 1 — Jumlah Pesanan per Pelanggan#

Tampilkan:

• Nama pelanggan.
• Jumlah pesanan.

Tampilkan semua pelanggan, termasuk yang belum pernah memesan.

Urutkan berdasarkan jumlah pesanan terbanyak.

Latihan 2 — Produk dengan Penjualan Lebih dari Satu Unit#

Tampilkan:

• Nama produk.
• Total unit terjual.

Hanya tampilkan produk dengan total unit terjual lebih dari satu.

Gunakan GROUP BY dan HAVING.

Latihan 3 — Produk di Atas Rata-Rata Harga#

Tampilkan nama dan harga produk yang harganya lebih besar dari rata-rata harga seluruh produk.

Gunakan subquery.

Latihan 4 — Pelanggan Tanpa Pesanan#

Tampilkan pelanggan yang belum pernah membuat pesanan.

Gunakan NOT EXISTS.

Latihan 5 — Pesanan dengan Total di Atas Rp500.000#

Tampilkan:

• ID pesanan.
• Total nilai pesanan.

Hanya tampilkan pesanan yang totalnya lebih dari Rp500.000.

Urutkan berdasarkan total terbesar.

Coba kerjakan sendiri sebelum melihat pembahasannya.

Jawaban Latihan 1#

1
SELECT
2
    pl.id_pelanggan,
3
    pl.nama_pelanggan,
4
    COUNT(p.id_pesanan) AS jumlah_pesanan
5
FROM pelanggan pl
6
LEFT JOIN pesanan p
7
    ON pl.id_pelanggan = p.id_pelanggan
8
GROUP BY
9
    pl.id_pelanggan,
10
    pl.nama_pelanggan
11
ORDER BY jumlah_pesanan DESC, pl.nama_pelanggan ASC;

Karena query ini hanya menggabungkan pelanggan dan pesanan, setiap baris pesanan dihitung satu kali.

Jika kita ikut menggabungkan detail pesanan, perlu memperhatikan potensi penghitungan ganda.

Jawaban Latihan 2#

1
SELECT
2
    pr.id_produk,
3
    pr.nama_produk,
4
    SUM(dp.jumlah) AS total_terjual
5
FROM produk pr
6
INNER JOIN detail_pesanan dp
7
    ON pr.id_produk = dp.id_produk
8
GROUP BY
9
    pr.id_produk,
10
    pr.nama_produk
11
HAVING SUM(dp.jumlah) > 1
12
ORDER BY total_terjual DESC;

Jawaban Latihan 3#

1
SELECT
2
    id_produk,
3
    nama_produk,
4
    harga
5
FROM produk
6
WHERE harga > (
7
    SELECT AVG(harga)
8
    FROM produk
9
)
10
ORDER BY harga DESC;

Jawaban Latihan 4#

1
SELECT
2
    pl.id_pelanggan,
3
    pl.nama_pelanggan
4
FROM pelanggan pl
5
WHERE NOT EXISTS (
6
    SELECT 1
7
    FROM pesanan p
8
    WHERE p.id_pelanggan = pl.id_pelanggan
9
);

Jawaban Latihan 5#

1
SELECT
2
    id_pesanan,
3
    SUM(jumlah * harga_satuan) AS total_pesanan
4
FROM detail_pesanan
5
GROUP BY id_pesanan
6
HAVING SUM(jumlah * harga_satuan) > 500000
7
ORDER BY total_pesanan DESC;

Jika data sesuai contoh Bab 5, kedua pesanan memenuhi kondisi tersebut.

Andi memiliki total Rp595.000 dan Budi Rp1.650.000.