Opsummer dine s .l-resultater med GROUP BY-klausulen

i dagens lektion lærer du om gruppering af resultater, der returneres fra dine forespørgsler ved hjælp af S .l GROUP BY-klausulen.

mål af dagens lektie er:

• Lær at gruppen resultater ved hjælp af GROUP BY
• Brug samlede funktioner til at foretage beregninger
• Forstå, hvordan man filter grupper, der bruger HAVING

Vigtigt! Følg med og gøre eksemplerne i din database. Hvis du ikke allerede har gjort det, skal du tilmelde dig min Guide til at komme i gang med s .l Server., Du får instruktioner om, hvordan du installerer de gratis værktøjer og prøve database.

s .l-gruppe efter Klausul

S .l-gruppe efter Klausul bruges til at udsende en række på tværs af specificerede kolonneværdier. Det bruges typisk sammen med aggregerede funktioner såsom SUM eller Count for at opsummere værdier. I S .l grupper er unikke kombinationer af felter. I stedet for at returnere hver række i en tabel, når værdier er grupperet, returneres kun de unikke kombinationer.

gruppen efter Klausul føjes til S .l-sætningen efter Clausehere-klausulen., Her er et eksempel, hvor vi notering SalesOrderID, eksklusive mængder større end 100.

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

Der er et par ting at bemærke. For det første er de kolonner, vi vil opsummere, angivet, adskilt af kommaer, i gruppen efter klausul. For det andet skal denne samme liste over kolonner vises i select-sætningen; ellers mislykkes sætningen.,

når denne erklæring køres, returneres ikke hver filtreret række. Kun unikke kombinationer af SalesOrderID er inkluderet i resultatet. Denne erklæring er meget lig

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

Men der er en afgørende forskel., Den distinkte modifikator stopper ved at udsende en unik kombination af rækker, hvorimod vi med gruppen ved erklæring kan beregne værdier baseret på de underliggende filtrerede rækker for hver unik kombination.

med andre ord, ved brug af vores eksempel, med GRUPPEN VED, kan vi beregne antallet eller OrderDetails per ordre som følgende:

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

COUNT er et eksempel på en samlet funktion, disse er, hvad der virkelig give den GRUPPE AF erklæring dens særlige værdi.

s .l aggregerede funktioner

nogle funktioner, såsom SUM, bruges til at udføre beregninger på en gruppe rækker, disse kaldes aggregerede funktioner. I de fleste tilfælde fungerer disse funktioner på en gruppe af værdier, der er defineret ved hjælp af GROUP BY-klausulen., Når der ikke er en gruppe for klausul, forstås det generelt, at aggregatfunktionen gælder for alle filtrerede resultater.

Nogle af de mest almindelige samlede funktioner kan nævnes:

Disse funktioner kan bruges på egen hånd i forbindelse med GROUP BY-delsætning. På egen hånd opererer de på tværs af hele tabellen; men når de bruges med GROUP BY, bliver deres beregninger “nulstillet” hver gang grupperingen ændres., På denne måde fungerer de som Subtotaler.

enkel Aggregatfunktion

Når du bruger aggregatfunktionen, kan du enten beregne resultatet på alle værdier eller forskellige værdier. For eksempel at tælle alle SalesOrderDetails optegnelser kunne vi bruge udtrykket:

SELECT COUNT(SalesOrderID)
FROM Sales.SalesOrderDetail

dette resulterer i en optælling af 121317 bliver returneret.

for at tælle de forskellige ordrer, der udgør detaljerne, ville vi bruge følgende:

SELECT COUNT(Distinct SalesOrderID)
FROM Sales.SalesOrderDetail

tællingen er nu 31465.,

s .l-gruppe efter og aggregerede funktioner

at aggregere midler til at gøre hele fra individuelle dele. Aggregerede funktioner er funktioner, der arbejder på mere end en række for at returnere et resultat.

gennemsnit og SUM

SUMFUNKTIONEN udgør de returnerede værdier, på lignende måde beregner AVG gennemsnittet.

lad os se, om vi kan beregne det samlede ordrebeløb fra ordredetaljer., Fra tidligere erfaringer ved vi, hvordan til at beregne det samlede beløb for hver detalje som:

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

Da vi kan anvende samlede funktion til udtryk, vi kan oprette en gruppe på OrderID til at beregne den samlede pris pr ordre, som

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

Vi kan også selv sortere efter alt at få den bedste ordrer første

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

På samme måde kan vi beregne gennemsnit for detaljer ved hjælp af AVG. Hvorfor prøver du det nedenfor! Hvis du går i stå skifte svaret.

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

For de nysgerrige, da et gennemsnit er beregnet som summen af stikprøven opdelt efter optællingen, så ved hjælp af AVG i ovenstående opgørelse er det samme som:

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

Vi dækket en masse i dette afsnit., Her er nogle vigtige punkter at huske på:

1. En samlet funktion kan evaluere et udtryk som SUM(A + B)
2. Du bør alias samlede funktioner, så de kolonne navne er meningsfuld
3. Når du arbejder med samlede funktioner og GROUP BY, er det nogle gange er nemmere at tænke på detaljerne første, der skriver en simpel SELECT-sætning, inspicere de resultater, og derefter tilføje i de fancy ting. Jeg dækker dette i, hvordan man skriver bedre forespørgsler.

s .l COUNT

TÆLLEFUNKTIONEN bruges, når du har brug for at vide, hvor mange poster der findes i en tabel eller i en gruppe., COUNT(*) tæller hver post i grupperingen; mens COUNT(e expressionpression) tæller hver post, hvor e expressionpressions resultat ikke er null. Du kan også bruge Distinct withith COUNT til at finde antallet af unikke værdier i en gruppe.

for At finde antallet af SalesOrderDetail Linjer per ordre

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

for At finde antallet af unikke salgs-ordrer pr produkt

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

MIN-og MAX –

Brug MIN-og MAX-til at finde den mindste og største værdier, henholdsvis inden for en tabel eller en gruppe.,for eksempel for at finde de mindste og største produktmængder, der er bestilt inden for en ordre, prøv

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

Du kan også finde min eller MA.værdien af en beregning. Her finder vi det højeste produktbeløb bestilt inden for et produkt:

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

under Klausul

s .l-haveklausulen bruges til at filtrere grupper i henhold til resultaterne af de samlede funktioner. Dette gør det muligt at løse problemer som at vælge alle ordrer, der har mere end to ordredetaljer.,

dette eksempel ligner

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

Hvis vi ønskede at finde ud af alle ordrer på mere end $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

Bemærk, at vi kan bruge alias TotalPrice i den RÆKKEFØLGE, SOM klausul, men så skulle en bestemmelse, der har til at bruge udtrykket.

for at hamre hjem med, vil jeg vise et sidste eksempel. Her ser du, at haver-erklæringen indeholder en samlet funktion, der ikke er i SELECT-listen.,

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

i ovenstående forespørgsel får vi den samlede pris for ordrer, hvor det gennemsnitlige SalesOrderDetail-beløb er større end $500.00.

endelig erklæring om at have

selvom de udfører en lignende funktion, er der en nøgleforskel mellem clausehere-klausulen og have. Clausehere-klausulen for at filtrere individuelle poster; der henviser til, at have-klausulen filtrerer på grupperne.

for at holde det lige i mit hoved kan jeg godt lide at tænke på clausehere-klausulen, der udfører sit arbejde, før nogen grupperinger finder sted, og derefter have-klausulen overtager efter grupperne er dannet.,

øvelser

det er vigtigt at øve! Brug prøvedatabasen til at besvare disse spørgsmål.

1. HR ønsker en rapport om antallet af aktive medarbejdere efter jobtitel. Hvad s ?l ville du bruge?
2. Vis min, Ma.og gennemsnitlig mængde bestilt for hvert produkt i SalesOrderDetails.
3. liste alle medarbejdernes jobtitler og antal ansatte, hvor det gennemsnitlige antal sygefraværstimer er mindre end eller lig med fyrre.
4. for en jobtitel returneret i #3 ovenfor, er tællingen den samme for den tilsvarende jobtitel svar #1 resultat?

svarene er her!,