Tipe-Tipe Group Functions

Resume5

STIKOM SURABAYA

Tipe-Tipe Group Functions

Setiap fungsi-fungsi menerima suatu argumen.

Pedoman-pedoman Untuk Menggunakan Group Functions

DISTINCT membuat suatu fungsi hanya mencakup nilainilai yang tidak sama (nonduplicate); ALL membuatnya mencakup setiap nilai, termasuk duplikatduplikat. Defaultnya adalah ALL dan karena itu tidak perlu ditentukan.

Tipe datatipe data untuk fungsi-fungsi dengan suatu argumen expr mungkin CHAR,VARCHAR2, NUMBER, atau DATE.

Semua Group functions mengabaikan nilai-nilai null. Untuk mengganti suatu nilai untuk nilai-nilai null, gunakanlah fungsi-fungsi NVL, NVL2, atau COALESCE.

Menggunakan Group Function

Menggunakan fungsi MAX dan MIN untuk tipe datatipe data numeric, character, dan date. Contoh pada slide menampilkan pegawai yang paling baru dan paling lama.

Contoh berikut ini menampilkan nama belakang pegawai yang pertama dan nama belakang

pegawai yang terakhir dalam suatu daftar abjad dari semua pegawai :

SELECT MIN(last_name), MAX(last_name)

FROM employees;

Catatan : Fungsifungsi

AVG, SUM, VARIANCE, dan STDDEV hanya dapat digunakan pada tipe data numeric, MAX dan MIN tidak dapat digunakan pada tipe datatipe data LOB atau LONG.

  • Fungsi COUNT

COUNT (*) : mengembalikan jumlah baris-baris dalam suatu tabel yang memenuhi kriteria dari

pernyataan SELECT, termasuk baris-baris yang sama dan baris-baris yang berisi nilai-nilai null di setiap kolom.

Jika suatu klausa WHERE adalah termasuk dalam pernyataan SELECT, COUNT (*) mengembalikan jumlah dari barisbaris yang memenuhi kondisi klausa WHERE.

COUNT (expr) : mengembalikan jumlah dari nilai-nilai nonnull yang berada dalam kolom yang diidentifikasi oleh expr

COUNT (DISTINCT expr) : mengembalikan jumlah dari nilai-nilai yang unik ,nonnull yang ada dalam kolom diidentifikasi oleh expr.

Group Functions dan Nilai-Nilai Null

Semua group functions mengabaikan nilai-nilai null dalam kolom.

Fungsi NVL memaksa group functions untuk menyertakan nilai-nilai null.

Contoh:

1. Rata-rata : dihitung berdasarkan hanya pada barisbaris dalam suatu tabel yang menyimpan nilai yang valid di kolom COMMISION_PCT. Ratarata dihitung sebagai total komisi yang dibayarkan ke semua pegawai dibagi dengan jumlah pegawai yang menerima komisi (empat).

2. Ratarata : dihitung berdasarkan semua barisbaris dalam suatu tabel, tidak peduli apakah nilainilai

di kolom COMMISION_PCT null atau bukan. Ratarata dihitung sebagai total komisi yang dibayarkan ke semua pegawai dibagi dengan jumlah total pegawai dalam perusahaan (20).

  • Klausa GROUP BY

Menggunakan klausa GROUP BY untuk membagi baris-baris dalam suatu table menjadi kelompok-kelompok.

Kemudian mendapat menggunakan group functions untuk mengembalikan informasi ringkas untuk setiap kelompok.

Dalam sintak :

Group_by_expression kolom-kolom tertentu yang nilai-nilainya menentukan dasar untuk pengelompokan baris-baris

  • Pedoman-pedoman

Jika anda menyertakan group functions pada klausa SELECT, anda tidak dapat memilih hasil-hasil secara individu dengan baik, kecuali kolom individu muncul pada klausa GROUP BY. Anda akan menerima pesan kesalahan jika anda keliru menyertakan daftar kolom di klausa GROUP BY.

Menggunakan klausa WHERE, anda dapat mengeluarkan barisbaris sebelum membaginya kedalam

Kelompok-kelompok.

Harus menyertakan kolom-kolom dalam klausa GROUP BY.

Anda tidak dapat menggunakan kolom alias dalam klausa GROUP BY.

Menggunakan Klausa GROUP BY

Ketika menggunakan klausa GROUP BY, pastikan bahwa semua kolom pada daftar SELECT yang bukan group functions disertakan pada klausa GROUP BY. Contoh pada slide menampilkan nomor departemen dan rata-rata penghasilan untuk tiap departemen. Berikut ini adalah bagaimana pernyataan SELECT, beserta klausa GROUP BY, dievaluasi :

Klausa SELECT menentukan kolomkolom yang akan diambil : Kolom nomor departement dalam tabel EMPLOYEES, Rata-rata dari semua penghasilan dalam suatu kelompok yang ditentukan oleh klausa GROUP BY.

Klausa FROM menentukan table-tabel yang harus diakses database : tabel EMPLOYEES.

Klausa WHERE menentukan barisbaris yang akan diambil. Karena tidak ada klausa WHERE, semua baris secara default akan diambil.

Klausa GROUP BY menentukan bagaimana baris-baris akan dikelompokkan. Baris-baris dikelompokkan berdasarkan nomor departemen, jadi fungsi AVG yang diterapkan pada kolom penghasilan akan menghitung ratarata gaji untuk tiap departemen.

SELECT department_id, AVG (salary)

FROM employee

GROUP BY department_id,

Tanpa nomor-nomor departemen, bagaimanapun, hasilnya akanmenjadi tidak ada artinya. Dapat menggunakan group functions pada klausa ORDER BY:

SELECT department_id, AVG(salary)

FROM employees

GROUP BY department_id

ORDER BY AVG(salary);

  • Kelompok Dalam Kelompok

Mengembalikan hasil ringkasan untuk kelompok dan subkelompok dengan mendaftar lebih dari satu kolom GROUP BY. Anda dapat menentukan default urutan pernyortiran suatu hasil berdasarkan urutan kolom-kolom pada klausa GROUP BY. Contoh pada slide, pernyataan SELECT yang menyertakan sebuah klausa GROUP BY yang dievalusi sebagai berikut:

Klausa SELECT menentukan kolom yang akan diambil : Nomor departement dalam tabel EMPLOYEES Job ID dalam tabel EMPLOYEES Total semua penghasilan dalam kelompok yang Anda tentukan pada klausa GROUP BY.

Klausa FROM menentukan table-tabel yang harus diakses database : tabel EMPLOYEES.

Klausa GROUP BY menentukan bagaimana Anda harus mengelompokkan barisbaris:

  1. Baris-baris dikelompokkan berdasarkan nomor departemen.
  2. Baris-baris dikelompokkan berdasar job ID dalam kelompok-kelompok nomor departemen.

Jadi fungsi SUM diterapkan ke kolom penghasilan untuk semua job ID di tiap kelompok nomor departemen.

  • Query-Query Ilegal Menggunakan Group Functions

Kapanpun menggunakan suatu campuran dari item-item individual (DEPARTMENT_ID) dan group function (COUNT) pada pernyataan SELECT yang sama, harus menyertakan suatu klausa GROUP BY yang menentukan item-item individu (dalam kasus ini, DEPARTMENT_ID). Jika klausa GROUP BY tidak ada, maka muncul pesan kesalahan “nota singlegroup group function” dan sebuah asterisk (*) akan menunjuk kepada kolom yang

bermasalah. Anda dapat memperbaiki kesalahan pada slide dengan menambahkan klausa GROUP BY :

SELECT department_id, count(last_name)

FROM employees

GROUP BY department_id;

Dapat memperbaiki kesalahan dengan menggunakan klausa HAVING untuk membatasi kelompok kelompok SELECT department_id, AVG(salary)

FROM employees

HAVING AVG(salary) > 8000

GROUP BY department_id;

  • Membatasi Hasil-Hasil Pengelompokkan dengan Klausa HAVING

Menggunakan klausa HAVING untuk menentukan pengelompokkan-pengelompokkan yang akan ditampilkan, lebih jauh lagi membatasi pengelompokkan berdasarkan informasi agreget. Dalam sintak, group_condition mengembalikan barisbaris pengelompokkan tertentu ke pengelompokkan tersebut yang memenuhi kondisi true.

Server Oracle melakukan langkah-langkah berikut ketika Anda menggunakan klausa HAVING:

1. Baris-baris dikelompokkan

2. Suatu Group function diterapkan pada pengelompokkan

3. Suatu pengelompokkan yang memenuhi kriteria pada klausa HAVING ditampilkan Klausa HAVING dapat mendahului klausa GROUP BY, tetapi disarankan bahwa Anda menempatkan GROUP BY lebih dulu karena lebih logis. Pengelompokkan adalah kondisi dan group functions dihitung sebelum klausa HAVING diterapkan ke pengelompokkan pada daftar SELECT.

SELECT department_id, AVG(salary)

FROM employees

GROUP BY department_id

HAVING max(salary)>10000;

  • Group Functions Bersarang

Group functions dapat disarangkan hingga 2 kedalaman. Contoh pada slide menampilkan

penghasilan rata-rata tertinggi.

Sumber :copyright@2004, oracle.

Leave a comment