Fungsi Grup

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

18.1. 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

18.1.1. 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;

18.2. 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;

18.3. 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.

Oracle Magazine contains technology strategy articles, sample code, tips, Oracle and partner news, how to articles for developers and DBAs, and more. Oracle (NASDAQ: ORCL) is the world’s largest enterprise software company.

For more information about Oracle, please visit their Web site at http://www.oracle.com. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

Get Free Oracle Magazine

18.3.1. 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;

Menampilkan data hari dan tanggal dalam bulan tertentu dengan bentuk kalender.

SELECT DISTINCT SUM(DECODE(HARI,’MON’,TO_CHAR(TGL,’DD’))) SEN,
SUM(DECODE(HARI,’TUE’,TO_CHAR(TGL,’DD’))) SEL,
SUM(DECODE(HARI,’WED’,TO_CHAR(TGL,’DD’))) RAB,
SUM(DECODE(HARI,’THU’,TO_CHAR(TGL,’DD’))) KAM,
SUM(DECODE(HARI,’FRI’,TO_CHAR(TGL,’DD’))) JUM,
SUM(DECODE(HARI,’SAT’,TO_CHAR(TGL,’DD’))) SAB,
SUM(DECODE(HARI,’SUN’,TO_CHAR(TGL,’DD’))) MIN
FROM
(
SELECT (LAST_DAY(LAST_DAY(TO_DATE(’DEC2008′,’MONYYYY’)) - 32) + 1)+rownum-1
TGL,
TO_CHAR(((LAST_DAY(LAST_DAY(TO_DATE(’DEC2008′,’MONYYYY’)) - 32) +
1)+rownum-1),’DY’) HARI,
TO_CHAR(((LAST_DAY(LAST_DAY(TO_DATE(’DEC2008′,’MONYYYY’)) - 32) +
1)+rownum-1),’IW’) MINGGU
FROM   ALL_OBJECTS
WHERE  ((LAST_DAY(LAST_DAY(TO_DATE(’DEC2008′,’MONYYYY’)) - 32) +
1)+rownum-1)
BETWEEN (LAST_DAY(LAST_DAY(TO_DATE(’DEC2008′,’MONYYYY’)) - 32) + 1)
AND LAST_DAY(TO_DATE(’DEC2008′,’MONYYYY’))
)
GROUP BY MINGGU;

Untuk mengubah menjadi bulan yang lain, kita harus mengganti DEC2008 dengan bulan yang kita inginkan misalnya NOV2008, dan seterusnya.18.4. Query Ilegal Menggunakan Fungsi Grup

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;

18.5. 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;

18.6. 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;

18.7. Latihan

1. Tampilkan rata-rata gaji pegawai, gaji tertinggi, gaji terendah, dan jumlah gaji seluruh pegawai khusus untuk pegawai yang memiliki jabatan Sales Representative.

SELECT AVG(salary), MAX(salary), MIN(salary), SUM(salary)
FROM employee
WHERE UPPER(title) LIKE ‘SALES%’;

2. Tampilkan nama belakang pegawai yang memiliki urutan huruf depan paling awal paling akhir dari seluruh pegawai.

SELECT MIN(last_name), MAX(last_name)
FROM employee;

3. Tampilkan jumlah pegawai yang bekerja pada departemen dengan kode 31.

SELECT COUNT(*) “Employees of Department 31″
FROM employee
WHERE dept_id = 31;

4. Tampilkan kembali soal diatas khusus untuk pegawai yang mendapatkan komisi.

SELECT COUNT(commission_pct)
FROM employee
WHERE dept_id = 31;

5. Tampilkan kode departemen dan jumlah departemen dengan kode 41, dengan ketentuan tampilkan jumlah departemen dengan judul Number.

SELECT dept_id, COUNT(*) “Number”
FROM employee
WHERE dept_id = 41
GROUP BY dept_id;

6. Tampilkan nilai kredit pelanggan dan jumlah pelanggan untuk masing-masing nilai kredit tersebut.

SELECT credit_rating, COUNT(*) “# Cust”
FROM customer
GROUP BY credit_rating;

7. Tampilkan jabatan pegawai dan total gaji bulanan untuk setiap jabatan pegawai tersebut, tidak termasuk pegawai dengan jabatan Vice President, dan tampilkan secara urut berdasarkan total gaji bulanan. Tampilkan total gaji bulanan dengan judul PAYROLL.

SELECT title, SUM(salary) PAYROLL
FROM employee
WHERE title NOT LIKE ‘VP%’
GROUP BY title
ORDER BY SUM(salary);

8. Tampilkan gaji bulanan maksimum untuk setiap jabatan.

SELECT MAX(salary)
FROM employee
GROUP BY title;

9. Tampilkan kembali soal diatas dengan menyertakan pula jabatan pegawai.

SELECT title, MAX(salary)
FROM employee
GROUP BY title;

10. Tampilkan kode daerah dan jumlah departemen yang berada pada daerah tersebut dengan query berikut. Jika terjadi kesalahan, betulkan sehingga menjadi sebuah query yang benar.

SELECT region_id, COUNT(name)
FROM department;

11. Tampilkan jumlah pegawai untuk setiap kategori jabatan pada setiap departemen.

SELECT title, COUNT(last_name)
FROM employee
GROUP BY title;

12. Tampilkan jumlah pegawai untuk setiap departemen dalam setiap jabatan.

SELECT dept_id, title, COUNT(last_name)
FROM employee
GROUP BY dept_id, title;

13. Tampilkan kode departemen dan rata-rata gaji untuk setiap departemen tersebut khusus untuk rata-rata gaji diatas 2000.

SELECT dept_id, AVG(salary)
FROM employee
GROUP BY dept_id
HAVING AVG(salary) > 2000;

14. Tampilkan jabatan pegawai dan total gaji bulanan untuk setiap kategori jabatan tersebut khusus untuk total gaji bulanan diatas 5000 tidak termasuk jabatan Vice President dan tampilkan urut berdasarkan total gaji bulanan.

SELECT title, SUM(salary) PAYROLL
FROM employee
WHERE title NOT LIKE ‘VP%’
GROUP BY title
HAVING SUM(salary) > 5000
ORDER BY SUM(salary);

15. Tampilkan kode departemen dengan total gaji bulanan lebih dari 4000.

SELECT dept_id
FROM employee
GROUP BY dept_id
HAVING SUM(salary) > 4000;

16. Tampilkan total order tertinggi dan terendah. Beri judul dengan Highest dan Lowest.

SELECT MAX(total) “Highest”, MIN(total) “Lowest”
FROM orders;

17. Tampilkan jabatan pegawai dengan judul JOB, gaji bulanan maksimum dan minimum dari setiap jabatan tersebut dengan judul MAXIMUM dan MINIMUM, dan tampilkan urut berdasarkan jabatan.

SELECT title, MAX(salary) MAXIMUM, MIN(salary) MINIMUM
FROM employee
GROUP BY title
ORDER BY title;

18. Tampilkan jumlah manager dengan judul Number of Managers.

SELECT COUNT(DISTINCT manager_id) “Number of Managers”
FROM employee;

19. Tampilkan nomor order dan jumlah item untuk masing-masing order.

SELECT ord_id, count(*) NUMBER_OF_ITEMS
FROM items
GROUP BY ord_id;

20. Tampilkan kode atasan dan gaji terkecil pegawai dari atasan tersebut, khusus untuk gaji yang lebih besar dari 1000.

SELECT manager_id, MIN(salary)
FROM employee
GROUP BY manager_id
HAVING MIN(salary) >1000
ORDER BY MIN(salary);

21. Tampilkan kode produk dan berapa kali produk tersebut di-order dengan judul TIMES ORDERED. Tampilkan hanya untuk produk yang di-order paling tidak sebanyak 3 kali. Urutkan data berdasarkan kode produk.

SELECT product_id, count(*) “TIMES ORDERED”
FROM items
GROUP BY    product_id
HAVING count(*) >= 3
ORDER BY “TIMES ORDERED”;

22. Tampilkan kode daerah, nama daerah dan jumlah departemen dalam setiap daerah tersebut.

SELECT r.id, r.name, count(d.id) “# OF DEPT”
FROM department d, region r
WHERE d.region_id = r.id
GROUP BY r.id, r.name;

23. Tampilkan nomor oder dan total item untuk setiap order, dengan catatan yang ditampilkan hanya order dengan total item lebih dari 100.

SELECT ord_id, SUM(quantity) “TOTAL ITEM COUNT”
FROM items
GROUP BY ord_id
HAVING SUM(quantity) > 100;

24. Tampilkan nama pelanggan dan jumlah order untuk masing-masing pelanggan.

SELECT c.name, count(o.customer_id) “NUMBER_OF_ORDERS”
FROM customer c, orders o
WHERE o.customer_id = c.id
GROUP BY c.name;

Silahkan melanjutkan membaca pembahasan Menentukan Variable Saat Runtime

Catatan:
Untuk mempraktekkan artikel-artikel mengenai Oracle SQL & PL/SQL Anda dapat mendownload script table disini, lalu jalankan sesuai live demo (browser Anda harus mendukung Flash Player).