Oppsummer Din SQL Resultater med GRUPPEN AV punkt

I dagens leksjon, du kommer til å lære om gruppering av resultatene som returneres fra ditt søk ved å bruke SQL-GRUPPEN VED punkt.

målene for dagens leksjon er å:

• Lær hvordan å gruppere resultatene ved hjelp av GRUPPEN AV
• Bruk samlede funksjoner til å utføre beregninger
• Forstå hvordan å filtrere grupper bruker å HA punkt

Viktig! Vennligst følg med og gjøre eksemplene i din database. Hvis du ikke allerede har gjort det, registrer deg for min Guide til å Komme i Gang med SQL Server., Du får instruksjoner om hvordan du installerer gratis verktøy og sample database.

SQL GRUPPE AV Punkt

SQL GRUPPE AV Punkt er brukt for å generere en rad over angitte kolonnen verdier. Det brukes vanligvis i forbindelse med samlede funksjoner, for eksempel SUM eller Telle for å oppsummere verdier. I SQL grupper er unik kombinasjon av feltene. I stedet for å returnere hver rad i en tabell, når verdiene er gruppert, bare de unike kombinasjoner er returnert.

GRUPPEN AV Punkt er lagt til SQL-Setningen etter den WHERE-Setningsdel., Her er et eksempel hvor vi har en liste med SalesOrderID, unntatt mengder større enn 100.

SELECT SalesOrderIDFROM Sales.SalesOrderDetailWHERE OrderQty <= 100GROUP BY SalesOrderID

Det er et par ting å merke seg. Første søylene vi ønsker å oppsummere er oppført, atskilt med komma, i GRUPPEN AV klausulen. For det andre, den samme listen over kolonner må være oppført i select-setning, ellers uttalelsen mislykkes.,

Når dette utsagnet er kjørt, ikke hver filtrert rad er returnert. Bare unike kombinasjoner av SalesOrderID er inkludert i resultatet. Denne uttalelsen er svært lik

-- AnswerSELECT DISTINCT SalesOrderIDFROM Sales.SalesOrderDetailWHERE OrderQty <= 100

Men det er en viktig forskjell., Den DISTINKTE modifier stopper på å gi ut en unik kombinasjon av rader, mens, med GRUPPEN AV utsagn, kan vi beregne verdier basert på de underliggende filtrert rader for hver unike kombinasjon.

med andre ord, ved hjelp av vårt eksempel med GRUPPEN, kan vi beregne antall eller OrderDetails per ordre som følgende:

-- AnswerSELECT SalesOrderID, COUNT(SalesOrderID) recCountFROM Sales.SalesOrderDetailGROUP BY SalesOrderID

TELLER er et eksempel på en samlet funksjon, disse er det som virkelig gi GRUPPEN AV utsagn sin spesielle verdi.

SQL Samlede Funksjoner

Noen funksjoner, for eksempel SUM, er brukt til å utføre beregninger på en gruppe av rader, disse er kalt samlede funksjoner. I de fleste tilfeller er disse funksjonene fungerer på en gruppe av verdier som er definert ved hjelp av GROUP BY-setningsdel., Når det ikke er en GRUPPE AV punkt, er det generelt forstått det samlede funksjonen gjelder for alle filtrerte resultatene.

Noen av de vanligste samlede funksjoner inkluderer:

Disse funksjonene kan brukes på sine egne på i forbindelse med GRUPPEN AV klausulen. På sine egne, de opererer over hele bordet, men når den brukes med en GRUPPE AV sine beregninger, er «reset» hver gang gruppering endringer., På denne måten kan de fungere som delsummer.

Enkel Samlede Funksjonen

Når du bruker den samlede funksjonen kan du enten regne ut resultatet på alle verdier eller distinkte verdier. For eksempel, å telle alle SalesOrderDetails poster vi kunne bruke uttrykket:

SELECT COUNT(SalesOrderID)
FROM Sales.SalesOrderDetail

Dette resulterer i en opptelling av 121317 blir returnert.

for Å telle de forskjellige ordrer som gjør opp detaljene ville vi bruke følgende:

SELECT COUNT(Distinct SalesOrderID)
FROM Sales.SalesOrderDetail

Den teller nå er 31465.,

SQL-GRUPPEN VED og Samlede Funksjoner

for Å samle betyr å gjøre hel fra enkelte deler. Samlede funksjoner som fungerer på mer enn én rad for å gå tilbake til et resultat.

AVG og SUMMEN

summer-funksjonen summer opp verdiene som returneres, på samme måte AVG beregner gjennomsnittet.

La oss se om vi kan beregne den totale for beløp fra OrderDetails., Fra tidligere leksjoner vi vet hvordan vi skal beregne den totale mengden for hver detalj slik:

SELECT SalesOrderID,
ProductID,
OrderQty* UnitPrice As ExtendedPrice
FROM Sales.SalesOrderDetail

Siden vi kan bruke samlede funksjonen til uttrykk, vi kan sette opp en gruppering på OrderID for å beregne den totale prisen per ordre som

SELECT SalesOrderID, SUM(OrderQty * UnitPrice) AS TotalPriceFROM Sales.SalesOrderDetailGROUP BY SalesOrderID

Vi kan også sortere av det totale å få topp bestillinger første

SELECT SalesOrderID,
SUM(OrderQty * UnitPrice) AS TotalPrice
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
ORDER BY TotalPrice DESC

På samme måte kan vi beregne gjennomsnittet for detaljer ved hjelp AVG. Hvorfor gjør du prøve den under! Hvis du får problemer du vil veksle mellom å svare på.

-- AnswerSELECT SalesOrderID, AVG(OrderQty * UnitPrice) AS TotalPriceFROM Sales.SalesOrderDetailGROUP BY SalesOrderIDORDER BY TotalPrice DESC

For den nysgjerrige, siden gjennomsnittet er beregnet som summen av utvalget delt med prøve-teller, så bruker AVG i ovennevnte uttalelse er det samme som:

SELECT SalesOrderID,
SUM(OrderQty * UnitPrice) / COUNT(SalesOrderID) AS AvgOrderAmount FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID

Vi dekket et mye i denne delen., Her er noen viktige punkter å huske:

1. En samlet funksjon kan evaluere et uttrykk som SUMMEN av(A + B)
2. Du bør alias samlede funksjoner, så kolonne navn er meningsfylt
3. Når du arbeider med samlede funksjoner og GRUPPE AV, det er noen ganger lettere å tenke på detaljene for det første at det er å skrive en enkel SELECT-uttrykk, og kontrollere resultatene, og deretter legg til i fancy ting. Jeg dekke dette i Hvordan Skrive Bedre Spørringer.

SQL TELLER

TELLER funksjonen brukes når du trenger å vite hvor mange poster eksisterer i en tabell eller i en gruppe., COUNT(*) vil telle hver oppføring i gruppen, mens TELLE(uttrykk) teller alle poster der uttrykket resultatet er ikke null. Du kan også bruke Forskjellige med COUNT å finne antallet unike verdier i en gruppe.

for Å finne antall SalesOrderDetail Linjer per ordre

SELECT SalesOrderID,
COUNT(SalesOrderDetailID)
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID

for Å finne antall unike salgsordrer per produkt

SELECT ProductID,
COUNT(DISTINCT SalesOrderID)
FROM Sales.SalesOrderDetail
GROUP BY ProductID

MIN og MAX

Bruke MIN og MAX for å finne den minste og største verdier, henholdsvis i en tabell eller en gruppe.,

For eksempel, for å finne den minste og største produktet mengder bestilt innenfor en ordre prøv

SELECT SalesOrderID,
Min(OrderQty) AS MinQuantity,
Max(OrderQty) AS MaxQuantity
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID

Du kan også finne MIN eller MAKS-verdien av en beregning. Her finner vi den høyeste produktet mengden bestilt innenfor et produkt:

SELECT SalesOrderID,
MAX(UnitPrice * OrderQty) as MaxAmount
FROM Sales.SalesOrderDetail GROUP BY SalesOrderID

etter å HA Punkt

SQL å HA klausul brukes til å filtrere grupper i henhold til resultatet av den samlede sum av funksjoner. Dette gjør det mulig å løse problemer som for eksempel velge alle bestillinger som har mer enn to for detaljer linjer.,

Som eksempel ser ut som

SELECT SalesOrderID,
COUNT(SalesOrderDetailID)
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
HAVING Count(SalesOrderDetailID) > 2

Hvis vi ønsket å finne alle ordre større enn $1000 ville vi skrive

SELECT SalesOrderID,
SUM(UnitPrice * OrderQty) AS TotalPrice
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
HAVING SUM(UnitPrice * OrderQty) > 1000
ORDER BY TotalPrice DESC

Merk at vi bruker aliaset TotalPrice i ORDER BY-setningsdel, men deretter ha en klausul som har å bruke uttrykket.

for Å hammer hjem etter å HA, jeg ønsker å vise et siste eksempel. Her får du se å HA erklæringen inneholder en samlet funksjon som ikke er i VELGER du liste.,

SELECT SalesOrderID,
SUM(UnitPrice * OrderQty) AS TotalPrice
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
HAVING AVG(UnitPrice * OrderQty) > 500
ORDER BY TotalPrice DESC

I over spørring vi får den totale prisen for bestillinger der den gjennomsnittlige SalesOrderDetail beløpet er større enn $500.00.

Siste Uttalelse om å HA

selv Om de utfører en lignende funksjon, det er en viktig forskjell mellom HVOR punkt og HA. WHERE-betingelsen for å filtrere individuelle poster, mens det å HA klausul filtre på grupper.

for Å holde det rett i hodet mitt, og jeg liker å tenke på HVOR punkt å gjøre sitt arbeid før alle grupperinger finne sted, og deretter å HA klausul tar over etter at gruppene er dannet.,

Øvelser

Det er viktig å øve! Bruk eksempel database for å svare på disse spørsmålene.

1. HR ønsker en rapport om antall aktive ansatte med stillingstittel. Hva SQL ville du bruke?
2. Vis Min, Maks, og Gjennomsnittlig Antall bestilt for hvert produkt i SalesOrderDetails.
3. en Liste over alle ansatte titler, og antall ansatte, hvor gjennomsnittlig antall sykefravær timer er mindre enn eller lik førti.
4. For en jobb tittel returnert i #3 ovenfor, er det antall det samme for tilsvarende stilling svar #1 er resultatet?

Svar er Her!,