Pengenalan GROUP BY
“Nja, kamu perlu bilang kalau kita akan mengolah data penjualan tahun lalu bukan?”
“Iya, ada apa?”
“Kalau sudah ada datanya, kita pasti butuh mengelompokkannya dan menghitungnya berdasarkan penjualan setiap provinsi maupun dikelompokkan per bulan. Kalau kayak begitu kasusnya, pakai SQL bagaimana caranya ya?”
“Gambaranmu bagus sekali, Aksara. Saya bantu jelaskan ya. Untuk mengelompokkan data di SQL kita menggunakan GROUP BY Statement. GROUP BY statement akan mengelompokkan data yang bernilai sama ke dalam satu group, dan dengan menggunakan fungsi aggregate seperti (COUNT, MAX, MIN, SUM, AVG) kita bisa melakukan agregasi untuk untuk setiap group atau kelompok yang terbentuk. Biar lebih jelas, coba pelajari penulisan syntax-nya,” jelas Senja.
Aku pun menyimak syntax yang diberikan:
Hal penting yang perlu diperhatikan adalah:
- GROUP BY digunakan dengan SELECT, artinya kolom yang digunakan di GROUP BY statement, juga perlu ditempatkan di SELECT.
- GROUP BY ditempatkan setelah WHERE, tetapi jika tidak menggunakan WHERE maka langsung ditempatkan setelah FROM.
- Jika menggunakan ORDER BY, maka GROUP BY ditempatkan sebelum ORDER BY.
“Apakah kita bisa menggunakan group by untuk mengelompokkan data menggunakan 2 kriteria atau 2 kolom, Nja?” tanyaku penasaran.
“Tentu saja bisa. Group by bisa dilakukan dengan single column ataupun multiple column. Seperti ini contohnya:”
- Group by Single Column, data dikelompokkan menggunakan kriteria dari satu kolom saja, misalnya mengelompokkan data berdasarkan provinsi saja.
- Group by Multiple Column, data dikelompokkan menggunakan kriteria dari dua kolom atau lebih, misalnya mengelompokkan data berdasarkan province dan brand.
Group by Single Column
Fungsi Group by Single Column memastikan data dapat dikelompokkan menggunakan kriteria dari satu kolom saja, misalnya mengelompokkan data berdasarkan provinsi saja.
Contoh:
Group by Multiple Column
Dengan fungsi Group by Multiple Column, data dapat dikelompokkan menggunakan kriteria dari dua kolom atau lebih, misalnya mengelompokkan data berdasarkan province dan brand.
Contoh:
Fungsi Aggregate dengan Grouping
“Tambahan lagi, Aksara. Kamu masih ingatkan fungsi agregasi yang kita pelajari dan praktikkan sebelumnya? Pada fungsi itu kita belum menggunakan group by sehingga hasil SUM dan COUNT kita adalah hasil SUM dan COUNT dari seluruh baris yang ada di tabel data penjualan.”
“Ada data penjualannya kah, Nja? Biar lebih mudah membayangkan dan mempraktikkannya,” usulku.
Senja membuka data penjualan perusahaan tahun lalu untukku. Wow!
“Kalau begitu, kita praktik langsung saja. Sekarang coba kamu gunakan fungsi agregasi dan GROUP BY untuk menghitung total penjualan dari setiap provinsi di tahun 2019, dan kita bandingkan dengan hasil fungsi agregasi tanpa menggunakan group by,” pinta Senja.
Contoh aggregate dengan grouping:
Penggunaan CASE … WHEN….
“Wah, terima kasih Senja, aku sudah mulai paham menggunakan fungsi aggregate di SQL. Tapi aku mau tanya deh, kalau aku diminta untuk menambahkan kolom rekomendasi atau remark dari hasil agregasi data, misalnya nilai penjualan bulan Maret 2019 lebih dari 30M, maka diberikan rekomendasi/remark “Target Achieved”, kalau kurang diberi remark “Follow Up”. Bagaimana melakukannya di SQL ya?
“Itu mudah saja, Aksara. Itu bisa kamu lakukan dengan CASE - statement di SQL. CASE - statement ini sama dengan IF - THEN - ELSE di pemrograman pada umumnya. Syntax dari CASE – statement seperti ini. Coba lihat.”
CASE - Statement ditempatkan sesudah SELECT, berikut syntax-nya secara lengkap :
SELECT ColumnName1, ColumnName2,
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
WHEN conditionN THEN resultN
ELSE result
END as alias
FROM TableName;
CASE-statement akan mengevaluasi kondisi yang sudah ditentukan, dimulai dari condition1, dan akan mengembalikan hasil (result1), jika condition1 terpenuhi (TRUE). Jika tidak, maka condition2 akan dievaluasi, dan akan mengembalikan result2 jika condition2 terpenuhi, dst. Apabila tidak ada kondisi yang terpenuhi, maka result pada bagian ELSE yang akan dikembalikan.
Nah, silahkan praktikkan penggunaan CASE-Statement untuk membuat remark pada data aggregasi penjualan.
Tugas:
Dengan menggunakan data sales_retail_2019, buatlah syntax query yang menggunakan fungsi skalar MONTH() untuk mengubah order_date dari tanggal ke bulan, fungsi aggregate SUM() untuk menjumlahkan kolom item_price.
Tambahkan kolom remark menggunakan CASE… WHEN… statement. Jika sum(item_price) >= 30.000.000.000, maka remark-nya 'Target Achieved'; Jika sum(item_price) <= 25.000.000.000 maka remark-nya 'Less performed'; Selain itu, beri remark 'Follow Up'.
Tugas:
Gantilah tanda _ _ _ di code editor dengan yang sesuai seperti yang dideskripsikan pada soal di atas.
SELECT MONTH(order_date) AS order_month, sum(item_price) AS total_price,
CASE
WHEN sum(item_price) >= 30000000000 THEN 'Target Achived'
WHEN sum(item_price) <= 25000000000 THEN 'Follow Up'
ELSE 'Less Performed'
END as remark
FROM sales_retail_2019
GROUP BY MONTH(order_date);
Jika berhasil, tabel yang akan muncul adalah seperti berikut ini:
Proyek Pekerjaan - Analisis Penjualan Part 1
Aku pun membuka email proyek dari Senja sambil menyeruput boba milk tea favoritku.
Aksara, saya senang dengan perkembanganmu belakangan ini. Saya mau minta tolong agar kamu melakukan analisis penjualan di suatu store. Adapun laporan yang diminta sebagai berikut:
- Total jumlah seluruh penjualan (total/revenue).
- Total quantity seluruh produk yang terjual.
- Total quantity dan total revenue untuk setiap kode produk.
Tabel: tr_penjualan
## 1. Total jumlah seluruh penjualan (total/revenue).
SELECT sum(total) as total
FROM tr_penjualan;
## 2. Total quantity seluruh produk yang terjual.
SELECT sum(qty) as qty
FROM tr_penjualan;
## 3. Total quantity dan total revenue untuk setiap kode produk.
SELECT kode_produk, sum(qty) as qty, sum(total) as total
FROM tr_penjualan
GROUP BY kode_produk;
Proyek Pekerjaan - Analisis Penjualan Part 2
(lanjutan ...)
Saya mau minta tolong agar kamu melakukan analisis penjualan di suatu store. Adapun laporan yang diminta sebagai berikut:
- Rata - Rata total belanja per kode pelanggan.
- Selain itu, jangan lupa untuk menambahkan kolom baru dengan nama ‘kategori’ yang mengkategorikan total/revenue ke dalam 3 kategori: High: > 300K; Medium: 100K - 300K; Low: <100K.
Tabel: tr_penjualan
## 4. Rata - Rata total belanja per kode pelanggan.
SELECT kode_pelanggan, avg(total) as avg_total
FROM tr_penjualan
GROUP BY kode_pelanggan;
## 5. Selain itu, jangan lupa untuk menambahkan kolom baru dengan nama ‘kategori’ yang mengkategorikan total/revenue ke dalam 3 kategori: High: > 300K; Medium: 100K - 300K; Low: <100K.
SELECT kode_transaksi,kode_pelanggan,no_urut,kode_produk, nama_produk, qty, total,
CASE
WHEN total > 300000 THEN 'High'
WHEN total < 100000 THEN 'Low'
ELSE 'Medium'
END as kategori
FROM tr_penjualan;
- Penggunaan fungsi skalar dan fungsi aggregate dalam operasi string dan numerik di SQL database
- Penggunaan GROUP BY dalam mengelompokkan data dan memahami konsep menggabungkan GROUP BY dengan fungsi aggregate
- Penggunaan CASE Statement untuk struktur pengambilan keputusan.
- Mengerjakan mini project yang merupakan integrasi keseluruhan materi dan tentunya materi-materi pada modul-modul sebelumnya untuk menyelesaikan persoalan bisnis.
source:DQLAB
Related Posts :
Subscribe by Email
Follow Updates Articles from This Blog via Email
No Comments