Cara memperbaiki Kesalahan 'Kolom tidak valid dalam daftar pilih karena tidak terdapat dalam fungsi agregat atau klausa GROUP BY'

Kesalahan "Kolom tidak valid dalam daftar pilih karena tidak terdapat dalam fungsi agregat atau klausa GROUP BY"Yang disebutkan di bawah muncul saat Anda menjalankan"KELOMPOKKAN OLEH", Dan Anda telah menyertakan setidaknya satu kolom dalam daftar pilih yang bukan merupakan bagian dari grup menurut klausa atau tidak terdapat dalam fungsi agregat seperti maks (), min (), jumlah (), hitung () dan rata-rata (). Jadi untuk membuat kueri berfungsi, kita perlu menambahkan semua kolom non-agregat ke salah satu grup berdasarkan klausa jika memungkinkan dan tidak berdampak pada hasil atau menyertakan kolom ini dalam fungsi agregat yang sesuai, dan ini akan berfungsi seperti pesona. Kesalahan muncul di MS SQL tetapi tidak di MySQL.

Dua kata kunci "Kelompokkan menurut"Dan"fungsi agregat"Telah digunakan dalam kesalahan ini. Jadi kita harus mengerti kapan dan bagaimana menggunakannya.

Kelompokkan menurut klausa:

Ketika seorang analis perlu meringkas atau menggabungkan data seperti laba, rugi, penjualan, biaya, dan gaji, dll. Menggunakan SQL, "KELOMPOKKAN OLEH”Sangat membantu dalam hal ini. Misalnya, untuk menyimpulkan, penjualan harian untuk ditunjukkan kepada manajemen senior. Demikian pula, jika Anda ingin menghitung jumlah siswa di suatu departemen dalam kelompok universitas bersama dengan fungsi agregat akan membantu Anda mencapainya.

Kelompokkan menurut strategi Split-Apply-Combine:

Kelompokkan dengan menggunakan strategi “split-apply-gabungkan”

Pada gambar di atas kita dapat melihat bahwa kolom telah dipecah menjadi tiga kelompok berdasarkan kolom pertama C1, dan kemudian fungsi agregat diterapkan pada nilai yang dikelompokkan. Pada fase gabungan terakhir memberikan satu nilai untuk setiap grup.

Ini dapat dijelaskan menggunakan contoh di bawah ini. Pertama, buat database bernama "appuals".

Contoh:

Buat tabel "karyawan”Menggunakan kode berikut.

GUNAKAN [aplikasi] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo]. [Karyawan] ([e_id] [int] NOT NULL, [e_ename] [varchar] (50) NULL, [dep_id] [int] NULL, [gaji] [int] NULL, KONSTRAINT [PK_ karyawan] KUNCI UTAMA DIKLUSTER ([e_id] ASC) DENGAN (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE = ON_LOCKS = ON UTAMA]) AKTIF [UTAMA] SETEL ANSI_PADDING NONAKTIF

Sekarang, Masukkan data ke dalam tabel menggunakan kode berikut.

Masukkan ke dalam nilai karyawan (e_id, e_ename, dep_id, gaji) (101, 'Sadia', 1.6000), (102, 'Saba', 1.5000), (103, 'Sana', 2.4000), ( 104, 'Hammad', 2.3000), (105, 'Umer', 3.4000), (106, 'Kanwal', 3.2000)

Outputnya akan seperti ini.

Sekarang pilih data dari tabel dengan mengeksekusi pernyataan berikut.

pilih * dari karyawan

Outputnya akan seperti ini.

Sekarang kelompokkan menurut tabel menurut id departemen.

pilih dep_id, gaji dari kelompok karyawan menurut dep_id

Kesalahan: Kolom 'karyawan.sallary' tidak valid dalam daftar pilihan karena tidak terdapat dalam fungsi agregat atau klausa GROUP BY.

Error yang disebutkan di atas muncul karena query "GROUP BY" dijalankan dan Anda telah memasukkan kolom "employee.salary" dalam daftar pilih yang bukan merupakan bagian dari grup berdasarkan klausa atau termasuk dalam fungsi agregat.

Larutan:

Seperti yang kita ketahui itu "Dikelompokkan menurut" mengembalikan baris tunggal, jadi kita perlu menerapkan fungsi agregat ke kolom yang tidak digunakan dalam kelompok demi klausa untuk menghindari kesalahan ini. Terakhir, terapkan fungsi group by dan agregat untuk menemukan gaji rata-rata karyawan di setiap departemen dengan menjalankan kode berikut.

pilih dep_id, avg (gaji) sebagai average_sallary dari grup karyawan dengan dep_id

Selanjutnya, jika kita menggambarkan tabel ini menurut struktur split_apply_combine, maka akan terlihat seperti ini.

Gambar di atas menunjukkan bahwa pertama-tama tabel dikelompokkan menjadi tiga kelompok menurut id departemen, kemudian fungsi aggregate avg () diterapkan untuk mencari nilai rata-rata agregat gaji, yang kemudian digabungkan dengan id departemen. Dengan demikian tabel dikelompokkan berdasarkan id departemen dan gaji dikumpulkan berdasarkan departemen.

Fungsi agregat:

Deskripsi logis dari penggunaan fungsi kelompok oleh dan fungsi agregat bersama-sama:

Sekarang kita akan memahami penggunaan "kelompok oleh" dan "fungsi agregat" secara logis melalui sebuah contoh.

Buat tabel bernama "orang-orang”Di database dengan menggunakan kode berikut.

GUNAKAN [aplikasi] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo]. [Orang] ([id] [bigint] IDENTITAS (1,1) BUKAN NULL, [nama] [varchar] (500) NULL, [ city] [varchar] (500) NULL, [state] [varchar] (500) NULL, [age] [int] NULL) ON [PRIMARY] GO

Sekarang masukkan data ke dalam tabel menggunakan kueri berikut.

masukkan ke dalam orang (nama, kota, negara bagian, usia) nilai ('Meggs', 'MONTEREY', 'CA', 20), ('Staton', 'HAYWARD', 'CA', 22), ('Irons', 'IRVINE', 'CA', 25) ('Krank', 'PLEASANT', 'IA', 23), ('Davidson', 'WEST BURLINGTON', 'IA', 40), ('Pepewachtel', 'FAIRFIELD ',' IA ', 35) (' Schmid ',' HILLSBORO ',' OR ', 23), (' Davidson ',' CLACKAMAS ',' OR ', 40), (' Condy ',' GRESHAM ',' OR ', 35)

Outputnya akan seperti ini:

Jika analis perlu mengetahui tidak ada penduduk dan usia mereka di negara bagian yang berbeda. Kueri berikut akan membantunya mendapatkan hasil yang dibutuhkan.

pilih usia, hitung (*) sebagai no_of_residents dari kelompok orang menurut negara bagian

Kesalahan: Kolom 'orang.age' tidak valid dalam daftar pilihan karena tidak terdapat dalam fungsi agregat atau klausa GROUP BY.

Saat mengeksekusi kueri yang disebutkan di atas, kami menemukan kesalahan berikut

"Msg 8120, Level 16, State 1, Baris 16 Kolom 'people.age' tidak valid dalam daftar pilih karena tidak terkandung dalam fungsi agregat atau klausa GROUP BY".

Kesalahan ini muncul karena “GROUP BY” query dijalankan dan Anda telah memasukkannya "'orang-orang. usia" kolom dalam daftar pilih yang bukan merupakan bagian dari grup dengan klausa atau termasuk dalam fungsi agregat.

Pengelompokan menurut negara memunculkan kesalahan

Deskripsi logis dan Solusi:

Ini bukan kesalahan sintaks, tetapi kesalahan logis. Seperti yang dapat kita lihat bahwa kolom "no_of_residents" mengembalikan hanya satu baris, sekarang bagaimana kita bisa mengembalikan usia semua penduduk dalam satu kolom? Kami dapat memiliki daftar usia orang yang dipisahkan dengan koma atau usia rata-rata, usia minimum atau maksimum. Oleh karena itu kami membutuhkan lebih banyak informasi tentang kolom "usia". Kita harus mengukur apa yang kita maksud dengan kolom usia. Dengan usia apa kita ingin dikembalikan. Sekarang kita dapat mengubah pertanyaan kita dengan informasi yang lebih spesifik tentang kolom usia seperti ini.

Temukan jumlah penduduk beserta usia rata-rata penduduk di setiap negara bagian. Mempertimbangkan ini, kami harus memodifikasi kueri kami seperti yang ditunjukkan di bawah ini.

pilih negara bagian, rata-rata (usia) sebagai Umur, hitung (*) sebagai no_of_residen dari kelompok orang menurut negara bagian

Ini akan dijalankan tanpa kesalahan dan hasilnya akan seperti ini.

Jadi, penting juga untuk berpikir secara logis tentang apa yang harus dikembalikan dalam pernyataan pilih.

Selain itu, poin-poin berikut harus dipertimbangkan sementara menggunakan "group by" untuk menghindari kesalahan.

Kelompokkan menurut dan nilai NULL:

Pertama, tambahkan baris lain ke dalam tabel bernama "orang" dengan kolom "negara bagian" kosong / null.

masukkan ke dalam nilai orang (nama, kota, negara bagian, umur) ('Kanwal', 'GRESHAM', '', 35)

Sekarang jalankan pernyataan berikut.

pilih negara bagian, rata-rata (usia) sebagai Umur, hitung (*) sebagai no_of_residen dari kelompok orang menurut negara bagian

Gambar berikut menunjukkan keluarannya. Anda dapat melihat nilai kosong di kolom negara dianggap sebagai grup terpisah.

Sekarang tambahkan tidak ada baris nol dengan memasukkan lebih banyak baris ke dalam tabel dengan null sebagai status.

masukkan ke dalam orang (nama, kota, negara bagian, usia) nilai ('Kanwal', 'IRVINE', 'NULL', 35), ('Krank', 'PLEASANT', 'NULL', 23)

Sekarang jalankan lagi kueri yang sama untuk memilih keluaran. Kumpulan hasil akan seperti ini.

Kita dapat melihat pada gambar ini bahwa kolom kosong dianggap sebagai grup terpisah dan kolom kosong dengan 2 baris dianggap sebagai grup terpisah lainnya dengan dua no penghuni. Beginilah cara kerja "kelompok demi".