Meringkas data dengan GROUP BY

Selesai

Meskipun fungsi agregat berguna untuk analisis, Anda mungkin ingin mengatur data Anda ke dalam subset sebelum meringkasnya. Di bagian ini, Anda akan mempelajari cara menyelesaikannya menggunakan klausa GROUP BY.

Menggunakan klausa GROUP BY

Seperti yang telah Anda pelajari, saat pernyataan SELECT Anda diproses, setelah klausa FROM dan klausa WHERE telah dievaluasi, tabel virtual dibuat. Konten tabel virtual sekarang tersedia untuk diproses lebih lanjut. Anda dapat menggunakan klausul GROUP BY untuk membavisi isi tabel virtual ini menjadi grup baris.

Untuk mengelompokkan baris, tentukan satu atau beberapa elemen dalam klausa GROUP BY:

GROUP BY <value1> [, <value2>, …]

GROUP BY membuat grup dan menempatkan baris ke dalam setiap grup seperti yang ditentukan oleh elemen yang ditentukan dalam klausa.

Misalnya, kueri berikut akan menghasilkan sekumpulan baris yang dikelompokkan, satu baris per CustomerID dalam tabel Sales.SalesOrderHeader . Cara lain untuk melihat proses GROUP BY, adalah bahwa semua baris dengan nilai yang sama untuk CustomerID akan dikelompokkan bersama-sama dan dikembalikan dalam satu baris hasil.

SELECT CustomerID
FROM Sales.SalesOrderHeader
GROUP BY CustomerID;

Kueri di atas setara dengan kueri berikut:

SELECT DISTINCT CustomerID
FROM Sales.SalesOrderHeader

Setelah klausa GROUP BY diproses dan setiap baris telah dikaitkan dengan grup, fase kueri yang lebih baru harus menggabungkan elemen baris sumber apa pun yang ada dalam daftar SELECT tetapi tidak muncul di daftar GROUP BY. Persyaratan ini akan berdampak pada cara Anda menulis klausa SELECT dan HAVING Anda.

Jadi, apa perbedaan antara menulis kueri dengan GROUP BY atau DISTINCT? Jika yang ingin Anda ketahui adalah nilai yang berbeda untuk CustomerID, tidak ada perbedaan. Tetapi dengan GROUP BY, kita dapat menambahkan elemen lain ke daftar SELECT yang kemudian dikumpulkan untuk setiap grup.

Fungsi agregat paling sederhana adalah COUNT(*). Kueri berikut mengambil 830 baris sumber asli dari CustomerID dan mengelompokkannya ke dalam 89 grup, berdasarkan nilai CustomerID . Setiap nilai CustomerID yang berbeda menghasilkan satu baris output dalam kueri GROUP BY

SELECT CustomerID, COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY CustomerID;

Untuk setiap nilai CustomerID , kueri mengagregasi dan menghitung baris, jadi hasilnya menunjukkan kepada kami berapa banyak baris dalam tabel SalesOrderHeader milik setiap pelanggan.

ID Pelanggan

OrderCount

1234

3

1005

1

Perhatikan bahwa GROUP BY tidak menjamin urutan hasil. Seringkali, sebagai hasil dari cara operasi pengelompokan dilakukan oleh prosesor kueri, hasilnya dikembalikan dalam urutan nilai grup. Namun, Anda tidak boleh mengandalkan perilaku ini. Jika Anda memerlukan hasil untuk diurutkan, Anda harus secara eksplisit menyertakan klausa ORDER:

SELECT CustomerID, COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY CustomerID
ORDER BY CustomerID;

Kali ini, hasilnya dikembalikan dalam urutan yang ditentukan:

ID Pelanggan

OrderCount

1005

1

1234

3

Klausa dalam pernyataan SELECT diterapkan dalam urutan berikut:

1. DARI
2. DIMANA
3. Kelompokkan menurut
4. MEMILIKI
5. PILIH
6. URUTKAN BERDASARKAN

Alias kolom ditetapkan dalam klausa SELECT, yang terjadi setelah klausa GROUP BY tetapi sebelum klausa ORDER BY. Anda dapat mereferensikan alias kolom dalam klausa ORDER BY, tetapi tidak dalam klausa GROUP BY. Kueri berikut akan mengakibatkan kesalahan nama kolom yang tidak valid :

SELECT CustomerID AS Customer,
       COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY Customer
ORDER BY Customer;

Namun, kueri berikut akan berhasil, mengelompokkan, dan mengurutkan hasilnya berdasarkan ID pelanggan.

SELECT CustomerID AS Customer,
       COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY CustomerID
ORDER BY Customer;

Pemecahan masalah kesalahan GROUP BY

Hambatan umum untuk menjadi nyaman menggunakan GROUP BY dalam pernyataan SELECT adalah memahami mengapa jenis pesan kesalahan berikut terjadi:

Msg 8120, Level 16, State 1, Line 2 Column <column_name> tidak valid dalam daftar pilih karena tidak terkandung dalam fungsi agregat atau klausa GROUP BY.

Misalnya, kueri berikut diizinkan karena setiap kolom dalam daftar SELECT adalah kolom dalam klausa GROUP BY atau fungsi agregat yang beroperasi pada setiap grup:

SELECT CustomerID, COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY CustomerID;

Kueri berikut akan mengembalikan kesalahan karena PurchaseOrderNumber bukan bagian dari GROUP BY, dan tidak digunakan dengan fungsi agregat.

SELECT CustomerID, PurchaseOrderNumber, COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY CustomerID;

Kueri ini mengembalikan kesalahan:

Msg 8120, Level 16, State 1, Line 1
Column 'Sales.SalesOrderHeader.PurchaseOrderNumber' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Berikut adalah cara lain untuk memikirkannya. Kueri ini mengembalikan satu baris untuk setiap nilai CustomerID . Tetapi baris untuk CustomerID yang sama dapat memiliki nilai PurchaseOrderNumber yang berbeda, jadi manakah dari nilai yang harus dikembalikan?

Jika Anda ingin melihat pesanan per ID pelanggan dan per pesanan pembelian, Anda dapat menambahkan kolom PurchaseOrderNumber ke klausa GROUP BY, sebagai berikut:

SELECT CustomerID, PurchaseOrderNumber, COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY CustomerID, PurchaseOrderNumber;

Kueri ini akan mengembalikan satu baris untuk setiap pelanggan dan setiap kombinasi pesanan pembelian, bersama dengan jumlah pesanan untuk kombinasi tersebut.