Sådan løses fejlen 'Kolonne er ugyldig på listen, fordi den hverken er indeholdt i en samlet funktion eller GROUP BY-klausulen'

Fejlen “Kolonnen er ugyldig på listen, fordi den hverken er indeholdt i en samlet funktion eller GROUP BY-klausulen”Nævnt nedenfor opstår, når du udfører“GROUP BY”Forespørgsel, og du har inkluderet mindst en kolonne på listen, der hverken er en del af gruppen efter klausul, eller den er indeholdt i en samlet funktion som max (), min (), sum (), count () og gennemsnit (). Så for at få forespørgslen til at fungere, er vi nødt til at tilføje alle ikke-aggregerede kolonner til hver gruppe efter klausul, hvis det er muligt og ikke har nogen indflydelse på resultaterne eller inkludere disse kolonner i en passende samlet funktion, og dette fungerer som en charme. Fejlen opstår i MS SQL, men ikke i MySQL.

To nøgleord “Gruppér efter”Og“samlet funktion”Er blevet brugt i denne fejl. Så vi skal forstå, hvornår og hvordan vi bruger dem.

Gruppér efter klausul:

Når en analytiker har brug for at opsummere eller samle data som fortjeneste, tab, salg, omkostninger og løn osv. Ved hjælp af SQL, “GROUP BY”Er meget nyttigt i denne henseende. For eksempel for at opsummere det daglige salg, der skal vises til den øverste ledelse. Tilsvarende, hvis du vil tælle antallet af studerende i en afdeling i en universitetsgruppe sammen med en samlet funktion, hjælper det dig med at nå dette.

Gruppér efter Split-Apply-Combine-strategi:

Gruppér efter "split-anvend-kombiner-strategi" -strategi

I figuren ovenfor kan vi se, at søjlen er opdelt i tre grupper baseret på første søjle C1, og derefter anvendes samlet funktion på grupperede værdier. Endelig tildeler kombinationsfase en enkelt værdi til hver gruppe.

Dette kan forklares ved hjælp af eksemplet nedenfor. Opret først en database med navnet "appuals".

Eksempel:

Opret en tabel “medarbejder”Ved hjælp af følgende kode.

BRUG [appuals] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO Opret TABEL [dbo]. [Medarbejder] ([e_id] [int] NOT NULL, [e_ename] [varchar] (50) NULL, [dep_id] [int] NULL, [løn] [int] NULL, KONSTRAINT [PK_medarbejder] PRIMÆR NØGLE KLUSTERET ([e_id] ASC) MED (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON_ ALLOW ON ON PRIMÆR)) TIL [PRIMÆR] GO INDSTILL ANSI_PADDING FRA GO

Indsæt nu data i tabellen ved hjælp af følgende kode.

Indsæt i medarbejder (e_id, e_ename, dep_id, løn) værdier (101, 'Sadia', 1.6000), (102, 'Saba', 1.5000), (103, 'Sana', 2.4000), ( 104, 'Hammad', 2.3000), (105, 'Umer', 3.4000), (106, 'Kanwal', 3.2000)

Outputtet vil være sådan.

Vælg nu data fra tabellen ved at udføre følgende udsagn.

vælg * fra medarbejder

Outputtet vil være sådan.

Gruppér nu efter tabellen i henhold til afdelingens id.

vælg dep_id, løn fra medarbejdergruppe efter dep_id

Fejl: Kolonne 'medarbejder.sallary' er ugyldig på listen, fordi den hverken er indeholdt i en samlet funktion eller GROUP BY-klausulen.

Den ovennævnte fejl opstår, fordi forespørgslen "GROUP BY" udføres, og du har medtaget "medarbejder.salar" -kolonnen i listen, som hverken er en del af gruppen efter klausul eller inkluderet i en samlet funktion.

Opløsning:

Som vi ved det “Gruppere efter” returner enkelt række, så vi er nødt til at anvende en samlet funktion til kolonner, der ikke bruges i gruppe efter klausul for at undgå denne fejl. Endelig skal du anvende gruppe efter og en samlet funktion for at finde medarbejderens gennemsnitlige løn i hver afdeling ved at udføre følgende kode.

vælg dep_id, gennemsnit (løn) som gennemsnit_sallar fra medarbejdergruppe efter dep_id

Desuden, hvis vi skildrer denne tabel i henhold til split_apply_combine struktur, vil den se sådan ud.

Ovenstående figur viser, at tabellen først og fremmest er grupperet i tre grupper efter afdelings-id, så anvendes samlet gennemsnit () -funktion for at finde samlet gennemsnitsværdi af løn, som derefter kombineres med afdeling id. Tabellen er således grupperet efter afdelings-id, og løn er aggregeret afdelingsmæssigt.

Samlede funktioner:

Den logiske beskrivelse af brugen af ​​gruppefunktioner og samlede funktioner sammen:

Nu vil vi forstå brugen af ​​"gruppere efter" og "samlede funktioner" logisk via et eksempel.

Opret en tabel med navnet “mennesker”I databasen ved hjælp af følgende kode.

BRUG [appuals] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo]. [People] ([id] [bigint] IDENTITY (1,1) NOT NULL, [name] [varchar] (500) NULL, [ by] [varchar] (500) NULL, [state] [varchar] (500) NULL, [age] [int] NULL) ON [PRIMARY] GO

Indsæt nu data i tabellen ved hjælp af følgende forespørgsel.

indsæt værdier for mennesker (navn, by, stat, alder) ('Meggs', 'MONTEREY', 'CA', 20), ('Staton', 'HAYWARD', 'CA', 22), ('Jern', '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 ',' ELLER ', 35)

Outputtet vil være som:

Hvis analytikeren behøver at vide noget om beboerne og deres alder i de forskellige stater. Følgende forespørgsel hjælper ham med at få de krævede resultater.

vælg alder, tæl (*) som ingen_af_beboere fra folkegruppen efter stat

Fejl: Kolonne 'people.age' er ugyldig på listen, fordi den hverken er indeholdt i en samlet funktion eller GROUP BY-klausulen.

Ved udførelse af ovennævnte forespørgsel stødte vi på følgende fejl

“Msg 8120, niveau 16, tilstand 1, linje 16 Kolonne‘ people.age ’er ugyldig på listen, fordi den hverken er indeholdt i en samlet funktion eller GROUP BY-klausulen”.

Denne fejl opstår, fordi “GROUP BY” forespørgsel udføres, og du har inkluderet "'mennesker. alder" kolonne i listen, der hverken er en del af gruppen efter klausul eller inkluderet i en samlet funktion.

Gruppering efter stat opstår en fejl

Logisk beskrivelse og løsning:

Dette er ikke en syntaksfejl, men det er en logisk fejl. Da vi kan se, at kolonnen "nej_af_beboere" kun vender tilbage en enkelt række, hvordan kan vi nu returnere alderen for alle beboere i en enkelt kolonne? Vi kan have en liste over aldersgrupper adskilt af kommaer eller gennemsnitsalderen, minimums- eller maksimumsalderen. Derfor har vi brug for flere oplysninger om kolonnen "alder". Vi skal kvantificere, hvad vi mener med alderssøjlen. Efter alder, hvad vi ønsker at blive returneret. Nu kan vi ændre vores spørgsmål med mere specifikke oplysninger om alderssøjlen som denne.

Find ingen af ​​beboerne sammen med gennemsnitsalderen for beboere i hver stat. I betragtning af dette er vi nødt til at ændre vores forespørgsel som vist nedenfor.

vælg tilstand, gennemsnit (alder) som Alder, tæl (*) som ingen_af_beboere fra personer grupperet efter stat

Dette udføres uden fejl, og output vil være sådan.

Så det er også vigtigt at tænke logisk over, hvad man skal returnere i den valgte erklæring.

Desuden skal følgende punkter tages i betragtning mens ved hjælp af "gruppere efter" for at undgå fejl.

Gruppér efter og NULL-værdier:

Først skal du tilføje endnu en række i tabellen med navnet "folk" med kolonnen "tilstand" som tom / null.

indsæt i mennesker (navn, by, stat, alder) værdier ('Kanwal', 'GRESHAM', '', 35)

Udfør nu følgende erklæring.

vælg tilstand, gennemsnit (alder) som Alder, tæl (*) som ingen_af_beboere fra personer grupperet efter stat

Den følgende figur viser dens output. Du kan se tom værdi i tilstandskolonnen betragtes som en separat gruppe.

Forøg nu ingen null-rækker ved at indsætte flere rækker i tabellen med null som tilstand.

indsæt i mennesker (navn, by, stat, alder) værdier ('Kanwal', 'IRVINE', 'NULL', 35), ('Krank', 'PLEASANT', 'NULL', 23)

Udfør nu den samme forespørgsel igen for at vælge output. Resultatsættet bliver sådan.

Vi kan se i denne figur, at en tom kolonne betragtes som en separat gruppe, og nulskolonnen med 2 rækker betragtes som en anden separat gruppe med to antal beboere. Sådan fungerer "gruppere efter".