Oracle Grup Function
Tidak seperti fungsi baris tunggal (single row functions), fungsi grup (group functions) beroperasi pada kumpulan baris-baris data (rows) untuk menghasilkan satu hasil per kelompok. Kelompok-kelompok ini bisa merupakan seluruh isi table atau table yang dibagi menjadi beberapa kelompok. Fungsi grup tampil pada klausa SELECT dan HAVING.
Group Functions
• AVG
• COUNT
• MAX
• MIN
• STDDEV
• SUM
• VARIANCE
Klausa GROUP BY dan HAVING di dalam perintah SELECT
Secara default, seluruh row di dalam table diperlakukan sebagai satu grup. Kita menggunakan klausa GROUP BY di dalam SELECT untuk membagi baris-baris data menjadi grup-grup yang lebih kecil. Sebagai tambahan, untuk membatasi hasil dari grup yang dikembalikan, kita gunakan klausa HAVING.
Sintaks
SELECT column, group_functions
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column]
dimana:
group_by_expression
menentukan kolom-kolom yang nilai-nilainya menentukan dasar untuk baris-baris yang dikelompokkan.
group_condition
membatasi grup-grup dari baris-baris data yang dihasilkan untuk grup-grup yang mana kondisi yang ditentukan bernilai TRUE.
Setiap fungsi menerima argumen. Daftar berikut mengidentifikasi opsi-opsi yang dapat kita gunakan dalam sintaks.
AVG(DISTINCT|ALL|n)
Nilai rata-rata dari n, mengabaikan nilai null
COUNT(DISTINCT|ALL|expr|*)
Jumlah baris data, dimana expr mengevaluasi sesuatu yang bukan null. Untuk menghitung seluruh baris data gunakan *, termasuk nilai-nilai duplikat dan baris-baris data yang mengandung nilai null
MAX(DISTINCT|ALL|expr)
Nilai maksimum dari expr
MIN(DISTINCT|ALL|expr)
Nilai minimum dari expr
STDDEV(DISTINCT|ALL|n)
Standar deviasi dari n, mengabaikan nilai null
SUM(DISTINCT|ALL|n)
Jumlah dari n, mengabaikan nilai null
VARIANCE(DISTINCT|ALL|n)
Varian dari n, mengabaikan nilai null
Aturan Penggunaan
DISTINCT menyebabkan fungsi hanya menganggap nilai non-duplikat; ALL menyebabkannya menganggap setiap nilai termasuk duplikat. Default-nya adalah ALL dan maka dari itu tidak perlu disebutkan.
Tipe data untuk argumen dapat berupa CHAR, VARCHAR2, NUMBER, atau DATE dimana expr disebutkan.
Seluruh fungsi grup kecuali COUNT(*) mengabaikan nilai null. Untuk mengganti nilai null, gunakan fungsi NVL.
Contoh:
Menampilkan nilai rata-rata, nilai tertinggi, nilai terendah, dan jumlah gaji bulanan untuk seluruh sales representative.
SELECT AVG(salaray), MAX(salary), MIN(salary), SUM(salary)
FROM employee
WHERE UPPER(title) LIKE ‘SALES%’;
Catatan:
Kita dapat menggunakan fungsi AVG dan SUM terhadap kolom yang menyimpan data numerik
Contoh:
Tampilkan nama belakang karyawan yang memiliki urutan abjad pertama dan yang terakhir.
SELECT MIN(last_name), MAX(last_name)
FROM employee;
Fungsi COUNT()
Fungsi COUNT memiliki dua bentuk: COUNT(*) dan COUNT(expr). COUNT(*) menghasilkan jumlah baris data di dalam table, termasuk baris-baris data duplikat dan baris-baris data yang mengandung nilai-nilai null.
Contoh:
Menampilkan jumlah employee yang bekerja di department 31.
SELECT COUNT(*)
FROM employee
WHERE dept_id = 31;
Menampilkan jumlah employee yang bekerja di department 31 dan memiliki komisi.
SELECT COUNT(commission_pct)
FROM employee
WHERE dept_id = 31;
Menampilkan data employee yang memiliki nama belakang kembar.
SELECT last_name, COUNT(1)
FROM employee
GROUP BY last_name
HAVING COUNT(1) > 1;
Klausa GROUP BY
Kita dapat menggunakan klausa GROUP BY untuk membagi baris-baris data di dalam table menjadi grup-grup yang lebih kecil. Kita kemudian dapat menggunakan fungsi grup untuk menghasilkan informasi jumlah untuk setiap grup.
Aturan Penggunaan
Jika kita menyertakan fungsi grup di dalam klausa SELECT, kita tidak dapat memilih hasil-hasil individual kecuali kolom individual tersebut tampil pada klausa GROUP BY.
Menggunakan klausa WHERE, kita dapat membatasi baris-baris data sebelum membaginya ke dalam grup.
Kita harus menyertakan kolom-kolom di dalam klausa GROUP BY.
Kita tidak dapat menggunakan notasi posisi atau alias kolom di dalam klausa GROUP BY.
Secara default, baris-baris data diurutkan secara menaik (ascending) pada GROUP BY. Kita dapat mengubah default ini dengan menggunakan klausa ORDER BY.
Contoh:
Menampilkan credit rating customer dan jumlah customer di setiap credit rating tersebut. Berikan label #Cust untuk kolom tersebut.
SELECT credit_rating, COUNT(*) ”#Cust”
FROM customer
GROUP BY credit_rating;
Contoh:
Menampilkan job title dan total salary bulanan untuk setiap job title tersebut, tidak termasuk vice president. Urutkan hasilnya berdasarkan jumlah salary bulanan.
SELECT title, SUM(salary) PAYROLL
FROM employee
WHERE title NOT LIKE ‘VP%’
GROUP BY title
ORDER BY SUM(salary);
Kolom-kolom yang digunakan untuk mengelompokkan hasil tidak harus disebutkan di dalam klausa SELECT seperti contoh berikut ini, tetapi hasilnya sangat tidak memiliki arti. Tambahkan kolom TITLE sehingga hasilnya menjadi memiliki arti.
Contoh:
Menampilkan salary maksimum untuk setiap job title, tanpa menampilkan job title.
SELECT MAX(salary)
FROM employee
GROUP BY title;
Menampilkan salary maksimum untuk setiap job title, dengan menampilkan job title.
SELECT title, MAX(salary)
FROM employee
GROUP BY title;
Kapanpun kita menggunakan gabungan item-item individual (REGION_ID) dan fungsi grup (COUNT) di dalam pernyataan SELECT yang sama, kita harus menyertakan klausa GROUP BY yang menentukan item-item individual (dalam kasus ini, REGION_ID). Jika klausa GROUP BY terlupakan, maka pesan kesalahan “not a singla group function” akan tampil dan tanda bintang (*) akan menunjuk kepada kolom salah.
Contoh:
Menampilkan region dan jumlah department yang berada di dalam region tersebut.
SELECT region_id, COUNT(name)
FROM department;
Betulkan kesalahan di atas dengan menambahkan klausa GROUP BY. Sekarang, REGION_ID menjadi nama grup.
Perhatian:
Setiap kolom atau ekspresi di dalam pernyataan SELECT yang bukan merupakan fungsi agregat harus berada di dalam klausa GROUP BY.
Klausa WHERE tidak dapat digunakan untuk membatasi grup.
Contoh:
Menampilkan department number dan rata-rata salary untuk tiap-tiap department tersebut yang memiliki rata-rata salary lebih dari 2000.
SELECT dept_id, AVG(salary)
FROM employee
WHERE AVG(salary) > 2000
GROUP BY dept_id;
Untuk membetulkan perintah diatas, gunakan klausa HAVING untuk membatasi hasil sesuai yang dikehendaki.
SELECT dept_id, AVG(salary)
FROM employee
GROUP BY dept_id
HAVING AVG(salary) > 2000;
Grup di dalam Grup
Kita dapat mendapatkan jumlah hasil untuk grup dan subgrup dengan menampilkan lebih dari satu kolom GROUP BY. Pada contoh kali ini, kita menghitung jumlah orang tidak hanya berdasarkan department, namun juga berdasarkan job category. Pengurutan kolom yang diletakkan di dalam klausa GROUP BY menentukan pengurutan secara default.
Contoh:
Menampilkan jumlah employee untuk setiap job category di dalam setiap department.
SELECT dept_id, title, COUNT(*)
FROM employee
GROUP BY dept_id, title;
Menampilkan jumlah employee untuk setiap department di dalam setiap job category.
SELECT title, dept_id, COUNT(*)
FROM employee
GROUP BY title, dept_id;
Klausa HAVING
Kita menggunakan klausa HAVING untuk menentukan grup mana yang akan ditampilkan. Sehingga, kita membatasi grup berdasarkan informasi agregat.
Oracle melakukan langkah-langkah berikut ketika kita menggunakan klausa HAVING:
Baris-baris data akan dikelompokkan
Fungsi grup digunakan
Grup-grup yang sesuai dengan kriteria di dalam klausa HAVING ditampilkan
Klausa HAVING boleh mendahului klausa GROUP BY, tetapi direkomendasikan agar kita meletakkan klausa GROUP BY terlebih dahulu karena lebih logik. Grup-grup dibentuk dan fungsi-fungsi grup dihitung sebelum klausa HAVING diterapkan kepada grup-grup di dalam pernyataan SELECT.
Contoh:
Menampilkan job title dan total salary bulanan untuk setiap job title dengan total pembayaran melebihi 5000. Jangan menyertakan vice president, dan urutkan hasilnya berdasarkan total salary bulanan.
SELECT title, SUM(salary) PAYROLL
FROM employee
WHERE title NOT LIKE ‘VP%’
GROUP BY title
HAVING SUM(salary) > 5000
ORDER BY SUM(salary);
Di dalam contoh berikut ini, tidak terdapat fungsi grup di dalam peryataan SELECT, tetapi terdapat klausa HAVING dan GROUP BY. Karena fungsi grup direferensi di dalam klausa HAVING, maka klausa GROUP BY diperlukan.
Contoh:
Menampilkan department number dengan total pembayaran salary bulanan melebihi 4000.
SELECT dept_id
FROM employee
GROUP BY dept_id
HAVING SUM(salary) > 4000;
Sumber : http://www.hastinapura.com/
Group Functions
• AVG
• COUNT
• MAX
• MIN
• STDDEV
• SUM
• VARIANCE
Klausa GROUP BY dan HAVING di dalam perintah SELECT
Secara default, seluruh row di dalam table diperlakukan sebagai satu grup. Kita menggunakan klausa GROUP BY di dalam SELECT untuk membagi baris-baris data menjadi grup-grup yang lebih kecil. Sebagai tambahan, untuk membatasi hasil dari grup yang dikembalikan, kita gunakan klausa HAVING.
Sintaks
SELECT column, group_functions
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column]
dimana:
group_by_expression
menentukan kolom-kolom yang nilai-nilainya menentukan dasar untuk baris-baris yang dikelompokkan.
group_condition
membatasi grup-grup dari baris-baris data yang dihasilkan untuk grup-grup yang mana kondisi yang ditentukan bernilai TRUE.
Setiap fungsi menerima argumen. Daftar berikut mengidentifikasi opsi-opsi yang dapat kita gunakan dalam sintaks.
AVG(DISTINCT|ALL|n)
Nilai rata-rata dari n, mengabaikan nilai null
COUNT(DISTINCT|ALL|expr|*)
Jumlah baris data, dimana expr mengevaluasi sesuatu yang bukan null. Untuk menghitung seluruh baris data gunakan *, termasuk nilai-nilai duplikat dan baris-baris data yang mengandung nilai null
MAX(DISTINCT|ALL|expr)
Nilai maksimum dari expr
MIN(DISTINCT|ALL|expr)
Nilai minimum dari expr
STDDEV(DISTINCT|ALL|n)
Standar deviasi dari n, mengabaikan nilai null
SUM(DISTINCT|ALL|n)
Jumlah dari n, mengabaikan nilai null
VARIANCE(DISTINCT|ALL|n)
Varian dari n, mengabaikan nilai null
Aturan Penggunaan
DISTINCT menyebabkan fungsi hanya menganggap nilai non-duplikat; ALL menyebabkannya menganggap setiap nilai termasuk duplikat. Default-nya adalah ALL dan maka dari itu tidak perlu disebutkan.
Tipe data untuk argumen dapat berupa CHAR, VARCHAR2, NUMBER, atau DATE dimana expr disebutkan.
Seluruh fungsi grup kecuali COUNT(*) mengabaikan nilai null. Untuk mengganti nilai null, gunakan fungsi NVL.
Contoh:
Menampilkan nilai rata-rata, nilai tertinggi, nilai terendah, dan jumlah gaji bulanan untuk seluruh sales representative.
SELECT AVG(salaray), MAX(salary), MIN(salary), SUM(salary)
FROM employee
WHERE UPPER(title) LIKE ‘SALES%’;
Catatan:
Kita dapat menggunakan fungsi AVG dan SUM terhadap kolom yang menyimpan data numerik
Contoh:
Tampilkan nama belakang karyawan yang memiliki urutan abjad pertama dan yang terakhir.
SELECT MIN(last_name), MAX(last_name)
FROM employee;
Fungsi COUNT()
Fungsi COUNT memiliki dua bentuk: COUNT(*) dan COUNT(expr). COUNT(*) menghasilkan jumlah baris data di dalam table, termasuk baris-baris data duplikat dan baris-baris data yang mengandung nilai-nilai null.
Contoh:
Menampilkan jumlah employee yang bekerja di department 31.
SELECT COUNT(*)
FROM employee
WHERE dept_id = 31;
Menampilkan jumlah employee yang bekerja di department 31 dan memiliki komisi.
SELECT COUNT(commission_pct)
FROM employee
WHERE dept_id = 31;
Menampilkan data employee yang memiliki nama belakang kembar.
SELECT last_name, COUNT(1)
FROM employee
GROUP BY last_name
HAVING COUNT(1) > 1;
Klausa GROUP BY
Kita dapat menggunakan klausa GROUP BY untuk membagi baris-baris data di dalam table menjadi grup-grup yang lebih kecil. Kita kemudian dapat menggunakan fungsi grup untuk menghasilkan informasi jumlah untuk setiap grup.
Aturan Penggunaan
Jika kita menyertakan fungsi grup di dalam klausa SELECT, kita tidak dapat memilih hasil-hasil individual kecuali kolom individual tersebut tampil pada klausa GROUP BY.
Menggunakan klausa WHERE, kita dapat membatasi baris-baris data sebelum membaginya ke dalam grup.
Kita harus menyertakan kolom-kolom di dalam klausa GROUP BY.
Kita tidak dapat menggunakan notasi posisi atau alias kolom di dalam klausa GROUP BY.
Secara default, baris-baris data diurutkan secara menaik (ascending) pada GROUP BY. Kita dapat mengubah default ini dengan menggunakan klausa ORDER BY.
Contoh:
Menampilkan credit rating customer dan jumlah customer di setiap credit rating tersebut. Berikan label #Cust untuk kolom tersebut.
SELECT credit_rating, COUNT(*) ”#Cust”
FROM customer
GROUP BY credit_rating;
Contoh:
Menampilkan job title dan total salary bulanan untuk setiap job title tersebut, tidak termasuk vice president. Urutkan hasilnya berdasarkan jumlah salary bulanan.
SELECT title, SUM(salary) PAYROLL
FROM employee
WHERE title NOT LIKE ‘VP%’
GROUP BY title
ORDER BY SUM(salary);
Kolom-kolom yang digunakan untuk mengelompokkan hasil tidak harus disebutkan di dalam klausa SELECT seperti contoh berikut ini, tetapi hasilnya sangat tidak memiliki arti. Tambahkan kolom TITLE sehingga hasilnya menjadi memiliki arti.
Contoh:
Menampilkan salary maksimum untuk setiap job title, tanpa menampilkan job title.
SELECT MAX(salary)
FROM employee
GROUP BY title;
Menampilkan salary maksimum untuk setiap job title, dengan menampilkan job title.
SELECT title, MAX(salary)
FROM employee
GROUP BY title;
Kapanpun kita menggunakan gabungan item-item individual (REGION_ID) dan fungsi grup (COUNT) di dalam pernyataan SELECT yang sama, kita harus menyertakan klausa GROUP BY yang menentukan item-item individual (dalam kasus ini, REGION_ID). Jika klausa GROUP BY terlupakan, maka pesan kesalahan “not a singla group function” akan tampil dan tanda bintang (*) akan menunjuk kepada kolom salah.
Contoh:
Menampilkan region dan jumlah department yang berada di dalam region tersebut.
SELECT region_id, COUNT(name)
FROM department;
Betulkan kesalahan di atas dengan menambahkan klausa GROUP BY. Sekarang, REGION_ID menjadi nama grup.
Perhatian:
Setiap kolom atau ekspresi di dalam pernyataan SELECT yang bukan merupakan fungsi agregat harus berada di dalam klausa GROUP BY.
Klausa WHERE tidak dapat digunakan untuk membatasi grup.
Contoh:
Menampilkan department number dan rata-rata salary untuk tiap-tiap department tersebut yang memiliki rata-rata salary lebih dari 2000.
SELECT dept_id, AVG(salary)
FROM employee
WHERE AVG(salary) > 2000
GROUP BY dept_id;
Untuk membetulkan perintah diatas, gunakan klausa HAVING untuk membatasi hasil sesuai yang dikehendaki.
SELECT dept_id, AVG(salary)
FROM employee
GROUP BY dept_id
HAVING AVG(salary) > 2000;
Grup di dalam Grup
Kita dapat mendapatkan jumlah hasil untuk grup dan subgrup dengan menampilkan lebih dari satu kolom GROUP BY. Pada contoh kali ini, kita menghitung jumlah orang tidak hanya berdasarkan department, namun juga berdasarkan job category. Pengurutan kolom yang diletakkan di dalam klausa GROUP BY menentukan pengurutan secara default.
Contoh:
Menampilkan jumlah employee untuk setiap job category di dalam setiap department.
SELECT dept_id, title, COUNT(*)
FROM employee
GROUP BY dept_id, title;
Menampilkan jumlah employee untuk setiap department di dalam setiap job category.
SELECT title, dept_id, COUNT(*)
FROM employee
GROUP BY title, dept_id;
Klausa HAVING
Kita menggunakan klausa HAVING untuk menentukan grup mana yang akan ditampilkan. Sehingga, kita membatasi grup berdasarkan informasi agregat.
Oracle melakukan langkah-langkah berikut ketika kita menggunakan klausa HAVING:
Baris-baris data akan dikelompokkan
Fungsi grup digunakan
Grup-grup yang sesuai dengan kriteria di dalam klausa HAVING ditampilkan
Klausa HAVING boleh mendahului klausa GROUP BY, tetapi direkomendasikan agar kita meletakkan klausa GROUP BY terlebih dahulu karena lebih logik. Grup-grup dibentuk dan fungsi-fungsi grup dihitung sebelum klausa HAVING diterapkan kepada grup-grup di dalam pernyataan SELECT.
Contoh:
Menampilkan job title dan total salary bulanan untuk setiap job title dengan total pembayaran melebihi 5000. Jangan menyertakan vice president, dan urutkan hasilnya berdasarkan total salary bulanan.
SELECT title, SUM(salary) PAYROLL
FROM employee
WHERE title NOT LIKE ‘VP%’
GROUP BY title
HAVING SUM(salary) > 5000
ORDER BY SUM(salary);
Di dalam contoh berikut ini, tidak terdapat fungsi grup di dalam peryataan SELECT, tetapi terdapat klausa HAVING dan GROUP BY. Karena fungsi grup direferensi di dalam klausa HAVING, maka klausa GROUP BY diperlukan.
Contoh:
Menampilkan department number dengan total pembayaran salary bulanan melebihi 4000.
SELECT dept_id
FROM employee
GROUP BY dept_id
HAVING SUM(salary) > 4000;
Sumber : http://www.hastinapura.com/
