Gunakan fungsi agregat

3 menit

T-SQL menyediakan fungsi agregat seperti SUM, MAX, dan AVG untuk melakukan penghitungan yang mengambil beberapa nilai dan mengembalikan satu hasil.

Bekerja dengan fungsi agregat

Sebagian besar kueri yang telah kita lihat beroperasi pada baris dan pada satu waktu, menggunakan klausa WHERE untuk memfilter baris. Setiap baris yang dikembalikan terkait dengan satu baris dalam kumpulan data asli.

Banyak fungsi agregat disediakan dalam Microsoft SQL Server. Di bagian ini, kita akan melihat fungsi yang paling umum seperti SUM, MIN, MAX, AVG, dan COUNT.

Saat bekerja dengan fungsi agregat, Anda perlu mempertimbangkan poin-poin berikut:

Fungsi agregat mengembalikan nilai (skalar) tunggal dan dapat digunakan dalam pernyataan SELECT hampir di mana saja nilai tunggal dapat digunakan. Misalnya, fungsi-fungsi ini dapat digunakan dalam klausa SELECT, HAVING, dan ORDER BY. Namun, mereka tidak dapat digunakan dalam klausa WHERE.
Fungsi agregat mengabaikan NULL, kecuali saat menggunakan COUNT(*).
Fungsi agregat dalam daftar SELECT tidak memiliki header kolom kecuali Anda menyediakan alias menggunakan AS.
Fungsi agregat dalam daftar SELECT beroperasi pada semua baris yang diteruskan ke operasi SELECT. Jika tidak ada klausa GROUP BY, semua baris yang memuaskan filter apa pun dalam klausa WHERE akan diringkas. Anda akan mempelajari selengkapnya tentang GRUP BY di topik berikutnya.
Kecuali Anda menggunakan GROUP BY, Anda tidak boleh menggabungkan fungsi agregat dengan kolom yang tidak disertakan dalam fungsi dalam daftar SELECT yang sama.

Untuk melampaui fungsi bawaan, Microsoft SQL Server menyediakan mekanisme untuk fungsi agregat yang ditentukan pengguna melalui .NET runtime bahasa umum (CLR). Topik ini berada di luar cakupan modul ini.

Fungsi agregat bawaan

Seperti disebutkan, T-SQL menyediakan banyak fungsi agregat bawaan. Fungsi yang umum digunakan meliputi:

Nama fungsi

Sintaksis

Keterangan

JUMLAH

SUM(ekspresi)

Menjumlahkan semua nilai numerik non-NULL dalam kolom.

Rata-rata

AVG(ekspresi)

Menjumlahkan semua nilai numerik non-NULL dalam kolom.

MIN

MIN(expression)

Mengembalikan angka terkecil, tanggal/waktu paling awal, atau string yang muncul pertama kali (sesuai dengan aturan pengurutan susunan).

MAKS

MAX(ekspresi)

Mengembalikan angka terkecil, tanggal/waktu paling awal, atau string yang muncul pertama kali (sesuai dengan aturan pengurutan susunan).

COUNT atau COUNT_BIG

COUNT(*) atau COUNT(expression)

Dengan (*), menghitung semua baris, termasuk baris dengan nilai NULL. Saat kolom ditentukan sebagai ekspresi,mengembalikan hitungan baris bukan NULL untuk kolom tersebut. COUNT mengembalikan int; COUNT_BIG mengembalikan big_int.

Untuk menggunakan agregat bawaan dalam klausa SELECT, pertimbangkan contoh berikut ini di database sampel MyStore:

SELECT AVG(ListPrice) AS AveragePrice,
       MIN(ListPrice) AS MinimumPrice,
       MAX(ListPrice) AS MaximumPrice
FROM Production.Product;

Hasil dari kueri ini mungkin terlihat seperti ini:

Harga Rata-rata

Harga Minimum

Harga Maksimal

744.5952

2.2900

3578.2700

Perhatikan bahwa contoh di atas meringkas semua baris dari tabel Production.Product. Kami dapat dengan mudah memodifikasi kueri untuk mengembalikan harga rata-rata, minimum, dan maksimum untuk produk dalam kategori tertentu dengan menambahkan klausa WHERE, seperti ini:

SELECT AVG(ListPrice) AS AveragePrice,
       MIN(ListPrice) AS MinimumPrice,
       MAX(ListPrice) AS MaximumPrice
FROM Production.Product
WHERE ProductCategoryID = 15;

Saat menggunakan agregat dalam klausa SELECT, semua kolom yang direferensikan dalam daftar SELECT harus digunakan sebagai input untuk fungsi agregat, atau direferensikan dalam klausa GROUP BY.

Pertimbangkan kueri berikut, yang mencoba menyertakan bidang ProductCategoryID dalam hasil agregat:

SELECT ProductCategoryID, AVG(ListPrice) AS AveragePrice,
MIN(ListPrice) AS MinimumPrice,
MAX(ListPrice) AS MaximumPrice
FROM Production.Product;

Menjalankan kueri ini menghasilkan kesalahan berikut

Msg 8120, Tingkat 16, Status 1, Jalur 1

Kolom 'Production.ProductCategoryID' tidak valid dalam daftar pilih karena tidak terkandung dalam fungsi agregat atau klausa GROUP BY.

Kueri memperlakukan semua baris sebagai grup agregat tunggal. Oleh karena itu, semua kolom harus digunakan sebagai input untuk menggabungkan fungsi.

Dalam contoh sebelumnya, kami menggabungkan data numerik seperti harga dan kuantitas dalam contoh sebelumnya. Beberapa fungsi agregat juga dapat digunakan untuk meringkas tanggal, waktu, dan data karakter. Contoh berikut menunjukkan penggunaan agregat dengan tanggal dan karakter:

Kueri ini mengembalikan nama perusahaan pertama dan terakhir, menggunakan MIN dan MAX:

SELECT MIN(CompanyName) AS MinCustomer, 
       MAX(CompanyName) AS MaxCustomer
FROM SalesLT.Customer;

Kueri ini akan mengembalikan nilai pertama dan terakhir untuk CompanyName dalam urutan kolase database, yang dalam hal ini adalah urutan alfabet:

MinCustomer

MaxCustomer

Toko Sepeda

Perusahaan Sepeda Kuning

Fungsi lain mungkin ditumpuk dengan fungsi agregat.

Misalnya, fungsi bernilai skalar YEAR digunakan dalam contoh berikut untuk mengembalikan hanya bagian tahun dari tanggal pesanan, sebelum MIN dan MAX dievaluasi:

SELECT MIN(YEAR(OrderDate)) AS Earliest,
       MAX(YEAR(OrderDate)) AS Latest
FROM Sales.SalesOrderHeader;

Awal

terbaru

2008

2021

Fungsi MIN dan MAX juga dapat digunakan dengan data tanggal, untuk mengembalikan nilai kronologis paling awal dan terbaru. Namun, AVG dan SUM hanya dapat digunakan untuk data numerik, yang mencakup bilangan bulat, uang, float, dan tipe data desimal.

Menggunakan DISTINCT dengan fungsi agregat

Anda harus mengetahui penggunaan DISTINCT dalam klausa SELECT untuk menghapus baris duplikat. Saat digunakan dengan fungsi agregat, DISTINCT menghapus nilai duplikat dari kolom input sebelum menghitung nilai ringkasan. DISTINCT berguna saat meringkas kemunculan nilai yang unik, seperti pelanggan dalam tabel pesanan.

Contoh berikut mengembalikan jumlah pelanggan yang telah melakukan pemesanan, tidak peduli berapa banyak pesanan yang mereka lakukan:

SELECT COUNT(DISTINCT CustomerID) AS UniqueCustomers
FROM Sales.SalesOrderHeader;

COUNT(<some_column>) hanya menghitung berapa banyak baris yang memiliki beberapa nilai dalam kolom. Jika tidak ada nilai NULL, COUNT(<some_column>) akan sama dengan COUNT(*). COUNT (DISTINCT <some_column>) menghitung berapa banyak nilai berbeda yang ada di kolom.

Menggunakan fungsi agregat dengan NULL

Penting untuk mengetahui kemungkinan adanya NULL dalam data Anda, dan bagaimana NULL berinteraksi dengan komponen kueri T-SQL, termasuk fungsi agregat. Ada beberapa pertimbangan yang harus diperhatikan:

Dengan pengecualian COUNT yang digunakan dengan opsi (*), fungsi agregat T-SQL mengabaikan NULL. Misalnya, fungsi SUM hanya akan menambahkan nilai non-NULL. NULL tidak mengevaluasi ke nol. COUNT(*) menghitung semua baris, terlepas dari nilai atau non-nilai di kolom mana pun.
Kehadiran NULL dalam kolom dapat menyebabkan komputasi yang tidak akurat untuk AVG, yang hanya akan menjumlahkan baris yang diisi dan membagi jumlah tersebut dengan jumlah baris non-NULL. Mungkin ada perbedaan hasil antara AVG(<column>) dan (SUM(<column>)/COUNT(*)).

Misalnya, pertimbangkan bernama t1 berikut:

NOL

Kueri ini mengilustrasikan perbedaan antara cara AVG menangani NULL dan cara Anda menghitung rata-rata dengan kolom yang dihitung SUM/COUNT(*):

SELECT SUM(c2) AS sum_nonnulls, 
    COUNT(*) AS count_all_rows, 
    COUNT(c2) AS count_nonnulls, 
    AVG(c2) AS average, 
    (SUM(c2)/COUNT(*)) AS arith_average
FROM t1;

Hasilnya adalah:

jumlah_nonnulls

hitung_semua_baris

hitung_tidak_nol

rata-rata

Rata-rata Aritmetika

150

Dua puluh lima

Dalam hasil ini, kolom bernama rata-rata adalah agregat yang secara internal mendapatkan jumlah 150 dan dibagi dengan hitungan nilai bukan nol di kolom c2. Perhitungannya adalah 150/5, atau 30. Kolom bernama arith_average secara eksplisit membagi jumlah dengan hitungan semua baris, sehingga penghitungannya adalah 150/6, atau 25.

Jika Anda perlu meringkas semua baris, baik NULL atau tidak, pertimbangkan untuk mengganti NULL dengan nilai lain yang tidak akan diabaikan oleh fungsi agregat Anda. Anda dapat menggunakan fungsi COALESCE untuk tujuan ini.

Saran dan Komentar

Apakah halaman ini membantu?