Hvordan fikse feilen ‘Kolonnen er ugyldig i listen, fordi den ikke er verken i en samlet funksjon eller GROUP BY-setningen’

Feilen "Kolonnen er ugyldig i listen, fordi den ikke er inkludert i en samlet funksjon eller GROUP BY-setningen”Nevnt nedenfor oppstår når du utfører“GRUPPE AV”-Spørring, og du har tatt med minst en kolonne i listen som hverken er en del av gruppen etter ledd, eller den er inneholdt i en samlet funksjon som maks (), min (), sum (), count () og avg (). Så for å få spørringen til å fungere, trenger vi å legge til alle ikke-aggregerte kolonner i en gruppe etter klausul hvis det er mulig og ikke har noen innvirkning på resultatene, eller inkludere disse kolonnene i en passende aggregasjonsfunksjon, og dette vil fungere som en sjarm. Feilen oppstår i MS SQL, men ikke i MySQL.

To nøkkelord “Gruppe av”Og”samlet funksjon”Har blitt brukt i denne feilen. Så vi må forstå når og hvordan vi bruker dem.

Gruppere etter paragraf:

Når en analytiker trenger å oppsummere eller samle data som fortjeneste, tap, salg, kostnad og lønn osv. Ved hjelp av SQL, “GRUPPE AV”Er veldig nyttig i denne forbindelse. For eksempel for å oppsummere, daglig salg for å vise til toppledelsen. Tilsvarende, hvis du vil telle antall studenter i en avdeling i en universitetsgruppe sammen med samlet funksjon, vil det hjelpe deg å oppnå dette.

Gruppere etter Split-Apply-Combine-strategi:

Grupper etter bruk "split-apply-combine" -strategi

I figuren ovenfor kan vi se at kolonnen er delt inn i tre grupper basert på første kolonne C1, og deretter blir samlet funksjon brukt på grupperte verdier. Til slutt tilordner kombinasjonsfasen en enkelt verdi til hver gruppe.

Dette kan forklares ved hjelp av eksemplet nedenfor. Først oppretter du en database som heter “appuals”.

Eksempel:

Lag en tabell “ansatt”Ved hjelp av følgende kode.

BRUK [appuals] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo]. [Ansatt] ([e_id] [int] NOT NULL, [e_ename] [varchar] (50) NULL, [dep_id] [int] NULL, [lønn] [int] NULL, CONSTRAINT [PK_medarbeider] PRIMÆR NØKKEL KLUSTERT ([e_id] ASC) MED (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON_ ALLOK = ON PRIMÆR)) PÅ [PRIMÆR] GO SET ANSI_PADDING OFF GO

Sett nå inn data i tabellen ved hjelp av følgende kode.

Sett inn i ansattes (e_id, e_ename, dep_id, lønn) verdier (101, 'Sadia', 1.6000), (102, 'Saba', 1.5000), (103, 'Sana', 2.4000), ( 104, 'Hammad', 2.3000), (105, 'Umer', 3.4000), (106, 'Kanwal', 3.2000)

Resultatet blir slik.

Velg nå data fra tabellen ved å utføre følgende påstand.

velg * fra ansatt

Resultatet blir slik.

Gruppér nå etter tabellen i henhold til avdelings-id.

velg dep_id, lønn fra ansattgruppe etter dep_id

Feil: Kolonne 'medarbeider.sallary' er ugyldig i utvalgslisten fordi den ikke er verken i en samlet funksjon eller GROUP BY-setningen.

Feilen nevnt ovenfor oppstår fordi "GROUP BY" -spørringen utføres, og du har tatt med "medarbeider.lønn" -kolonnen i utvalgslisten, som verken er en del av gruppen etter ledd eller inkludert i en samlet funksjon.

Løsning:

Som vi vet det "gruppe av" returner enkelt rad, så vi må bruke en samlet funksjon på kolonner som ikke brukes i gruppe etter klausul for å unngå denne feilen. Til slutt, bruk gruppe for og en samlet funksjon for å finne gjennomsnittlig lønn for den ansatte i hver avdeling ved å utføre følgende kode.

velg dep_id, avg (lønn) som gjennomsnittlig_sallary fra ansattgruppe etter dep_id

Videre, hvis vi skildrer denne tabellen i henhold til split_apply_combine struktur, vil den se slik ut.

Figuren over viser at først og fremst er tabellen gruppert i tre grupper i henhold til avdelings-id, deretter blir samlet avg () -funksjon brukt for å finne samlet gjennomsnittsverdi av lønn, som deretter kombineres med avdelings-id. Dermed er tabellen gruppert etter avdelings-id og lønn er aggregert avdelingsmessig.

Aggregerte funksjoner:

Den logiske beskrivelsen av bruken av gruppering av og samlede funksjoner sammen:

Nå vil vi forstå bruken av "gruppere etter" og "samle funksjoner" logisk via et eksempel.

Lag en tabell med navnet “mennesker”I databasen ved å bruke følgende kode.

BRUK [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

Sett nå inn data i tabellen ved hjelp av følgende spørsmål.

sett inn verdier for mennesker (navn, by, stat, alder) ('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 ',' ELLER ', 35)

Utgangen vil være som:

Hvis analytikeren trenger å vite noe om innbyggerne og deres alder i de forskjellige statene. Følgende spørsmål vil hjelpe ham med å få de nødvendige resultatene.

velg alder, tell (*) som ingen_innbyggere fra personer gruppert etter stat

Feil: Kolonnen ‘people.age’ er ugyldig i listen, fordi den ikke er inkludert i en samlet funksjon eller GROUP BY-setningen.

Ved utførelse av ovennevnte spørring, kom vi over følgende feil

“Msg 8120, Nivå 16, Tilstand 1, Linje 16 Kolonne‘ people.age ’er ugyldig i listen, fordi den ikke er inneholdt i verken en samlet funksjon eller GROUP BY-setningen.

Denne feilen oppstår fordi "GRUPPE AV" spørringen utføres, og du har tatt med "'mennesker. alder" kolonne i listen som hverken er en del av gruppen etter ledd eller inkludert i en samlet funksjon.

Gruppering etter stat oppstår en feil

Logisk beskrivelse og løsning:

Dette er ikke en syntaksfeil, men det er en logisk feil. Som vi kan se at kolonnen “nei_of_beboere” returnerer bare en enkelt rad, hvordan kan vi nå returnere alderen til alle innbyggerne i en enkelt kolonne? Vi kan ha en liste over alderen på personer atskilt med komma eller gjennomsnittsalder, minimums- eller maksimumsalder. Dermed trenger vi mer informasjon om "alder" -kolonnen. Vi må kvantifisere hva vi mener med alderskolonnen. Etter alder hva vi ønsker å bli returnert. Nå kan vi endre spørsmålet vårt med mer spesifikk informasjon om alderssøylen som dette.

Finn ingen av innbyggerne sammen med gjennomsnittsalderen for innbyggerne i hver stat. Tatt i betraktning dette må vi endre spørringen vår som vist nedenfor.

velg stat, gj.sn. (alder) som Alder, tell (*) som ingen_av_boende fra personer gruppert etter stat

Dette vil kjøres uten feil, og utdataene blir slik.

Så det er også avgjørende å tenke logisk om hva du skal returnere i den valgte uttalelsen.

Videre bør følgende punkter tas i betraktning mens bruke “gruppere etter” for å unngå feil.

Gruppere etter og NULL-verdier:

Først legger du til en ny rad i tabellen med navnet “personer” med “tilstand” -kolonnen som tom / null.

sett inn i mennesker (navn, by, stat, alder) verdier ('Kanwal', 'GRESHAM', '', 35)

Utfør nå følgende uttalelse.

velg stat, gj.sn. (alder) som Alder, tell (*) som ingen_av_boende fra personer gruppert etter stat

Følgende figur viser utdataene. Du kan se at tom verdi i tilstandskolonnen betraktes som en egen gruppe.

Øk nå ingen nullrader ved å sette inn flere rader i tabellen med null som tilstand.

sett inn verdier for mennesker (navn, by, stat, alder) ('Kanwal', 'IRVINE', 'NULL', 35), ('Krank', 'PLEASANT', 'NULL', 23)

Utfør nå den samme spørringen for å velge utdata. Resultatsettet blir slik.

Vi kan se i denne figuren at en tom kolonne blir betraktet som en egen gruppe og nullkolonnen med to rader betraktes som en annen separat gruppe med to antall innbyggere. Slik fungerer "gruppere etter".