i dagens lektion kommer du att lära dig om att gruppera resultat som returneras från dina frågor med hjälp av SQL GROUP BY-klausulen.
målen för dagens lektion är att:
- lär dig att gruppera resultat med hjälp av grupp med
- använd aggregerade funktioner för att utföra beräkningar
- förstå hur man filtrerar grupper med hjälp av ha klausulen
viktigt! Följ med och gör exemplen i din databas. Om du inte redan har gjort det, registrera dig för min Guide för att komma igång med SQL Server., Du får instruktioner om hur du installerar gratis verktyg och provdatabas.
SQL GROUP BY Clause
SQL GROUP BY Clause används för att mata ut en rad över angivna kolumnvärden. Det används vanligtvis i samband med aggregerade funktioner som summa eller räkna för att sammanfatta värden. I SQL grupper är unika kombinationer av fält. I stället för att returnera varje rad i en tabell, när värden grupperas, returneras endast de unika kombinationerna.
grupp efter Klausul läggs till i SQL-satsen efter WHERE-klausulen., Här är ett exempel där vi listar SalesOrderID, exklusive kvantiteter större än 100.
det finns ett par saker att notera. Först listas kolumnerna vi vill sammanfatta, åtskilda av kommatecken, i gruppen efter klausul. För det andra måste samma lista med kolumner listas i Select-satsen; annars misslyckas uttalandet.,
När detta uttalande körs returneras inte alla filtrerade rader. Endast unika kombinationer av SalesOrderID ingår i resultatet. Detta uttalande är mycket lik
men det finns en viktig skillnad., Den distinkta modifieraren stannar vid utmatning av en unik kombination av rader, medan vi med grupp för uttalande kan beräkna värden baserat på de underliggande filtrerade raderna för varje unik kombination.
med andra ord, med hjälp av vårt exempel, med gruppen av, kan vi beräkna numret eller Orderdetaljerna per order enligt följande:
COUNT är ett exempel på en aggregerad funktion, det är det som verkligen ger gruppen genom att uttrycka sitt speciella värde.
SQL Aggregate Functions
vissa funktioner, såsom SUM, används för att utföra beräkningar på en grupp rader, dessa kallas aggregatfunktioner. I de flesta fall fungerar dessa funktioner på en grupp av värden som definieras med hjälp av GROUP BY-klausulen., När det inte finns en grupp för klausul, är det allmänt förstått den aggregerade funktionen gäller för alla filtrerade resultat.
några av de vanligaste aggregeringsfunktionerna är:
| AVG(expression) | beräkna genomsnittet av uttrycket. |
| räkna(uttryck) | räkna förekomster av icke-null-värden som returneras av uttrycket. |
| COUNT (*) | räknar alla rader i den angivna tabellen., |
| MIN(expression) | hittar det minsta uttrycksvärdet. |
| MAX(uttryck) | hittar det maximala uttrycksvärdet. |
| SUM(expression) | beräkna summan av uttrycket. |
dessa funktioner kan användas på egen hand tillsammans med gruppen efter klausul. På egen hand fungerar de över hela tabellen; men när de används med grupp av är deras beräkningar ”Återställ” varje gång grupperingen ändras., På detta sätt fungerar de som delsummor.
enkel aggregeringsfunktion
När du använder aggregeringsfunktionen kan du antingen beräkna resultatet på alla värden eller distinkta värden. Till exempel, för att räkna alla SalesOrderDetails-poster kunde vi använda uttrycket:
SELECT COUNT(SalesOrderID)
FROM Sales.SalesOrderDetail
detta resulterar i att 121317 returneras.
för att räkna skillnaden mellan order som utgör detaljerna skulle vi använda följande:
SELECT COUNT(Distinct SalesOrderID)
FROM Sales.SalesOrderDetail
räkningen är nu 31465.,
SQL-KONCERNEN AV-och aggregeringsfunktionerna
för Att samla medel för att göra hela från enskilda delar. Aggregerade funktioner är funktioner som fungerar på mer än en rad för att returnera ett resultat.
AVG och SUM
SUMMERINGSFUNKTIONEN summerar de värden som returneras, på liknande sätt beräknar AVG medelvärdet.
låt oss se om vi kan beräkna det totala orderbeloppet från Orderdetaljerna., Från tidigare lektioner vet vi hur man beräknar det totala beloppet för varje detalj som:
SELECT SalesOrderID,
ProductID,
OrderQty* UnitPrice As ExtendedPrice
FROM Sales.SalesOrderDetail
eftersom vi kan använda aggregerad funktion för uttryck kan vi ställa in en gruppering på OrderID för att beräkna det totala priset per order som
SELECT SalesOrderID, SUM(OrderQty * UnitPrice) AS TotalPriceFROM Sales.SalesOrderDetailGROUP BY SalesOrderID
Vi kan även Sortera efter summan för att få topporderna först
SELECT SalesOrderID,
SUM(OrderQty * UnitPrice) AS TotalPrice
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
ORDER BY TotalPrice DESC
på liknande sätt kan vi beräkna den genomsnittliga orderdetalj med AVG. Varför försöker du det nedan! Om du fastnar växla svaret.
För Den nyfikna, eftersom ett genomsnitt beräknas som summan av provet dividerat med provräkningen, är det samma som att använda AVG i ovanstående uttalande:
SELECT SalesOrderID,
SUM(OrderQty * UnitPrice) / COUNT(SalesOrderID) AS AvgOrderAmount FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
vi täckte mycket i det här avsnittet., Här är några viktiga punkter att komma ihåg:
- en aggregerad funktion kan utvärdera ett uttryck som SUM(A + B)
- Du bör alias aggregerade funktioner, så kolumnnamnen är meningsfulla
- när man arbetar med aggregerade funktioner och grupp av, är det ibland lättare att tänka på detaljerna först, det är att skriva en enkel Välj uttalande, inspektera resultaten, sedan lägga till Jag täcker detta i hur man skriver bättre frågor.
SQL COUNT
COUNT-funktionen används när du behöver veta hur många poster som finns i en tabell eller i en grupp., COUNT(*) räknar varje post i grupperingen, medan COUNT(expression) räknar varje post där uttryckets resultat inte är noll. Du kan också använda Distinct med COUNT för att hitta antalet unika värden inom en grupp.
för att hitta antalet Försäljningsorderdetaljlinjer per order
SELECT SalesOrderID,
COUNT(SalesOrderDetailID)
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
för att hitta antalet unika försäljningsorder per produkt
SELECT ProductID,
COUNT(DISTINCT SalesOrderID)
FROM Sales.SalesOrderDetail
GROUP BY ProductID
min och MAX
använd MIN och MAX för att hitta de minsta och största värdena inom en tabell eller grupp.,
för att t.ex. hitta de minsta och största produktkvantiteterna som beställts inom en order, försök
SELECT SalesOrderID,
Min(OrderQty) AS MinQuantity,
Max(OrderQty) AS MaxQuantity
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
Du kan också hitta min-eller maxvärdet för en beräkning. Här hittar vi det högsta produktbeloppet som beställts inom en produkt:
SELECT SalesOrderID,
MAX(UnitPrice * OrderQty) as MaxAmount
FROM Sales.SalesOrderDetail GROUP BY SalesOrderID
med klausul
SQL HAVING-klausulen används för att filtrera grupper enligt resultaten av aggregerade funktioner. Detta gör det möjligt att lösa problem som att välja alla order som har mer än två orderdetaljer.,
det exemplet ser ut som
SELECT SalesOrderID,
COUNT(SalesOrderDetailID)
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
HAVING Count(SalesOrderDetailID) > 2
om vi ville hitta alla beställningar större än $1000 skulle vi skriva
SELECT SalesOrderID,
SUM(UnitPrice * OrderQty) AS TotalPrice
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
HAVING SUM(UnitPrice * OrderQty) > 1000
ORDER BY TotalPrice DESC
Observera att vi kan använda aliaset totalpris i ORDER BY-klausulen, men sedan har en klausul måste använda uttrycket.
för att hamra hem med, vill jag visa ett sista exempel. Här ser du uttrycket ha innehåller en samlad funktion som inte finns i listan Välj.,
SELECT SalesOrderID,
SUM(UnitPrice * OrderQty) AS TotalPrice
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
HAVING AVG(UnitPrice * OrderQty) > 500
ORDER BY TotalPrice DESC
i ovanstående fråga får vi det totala priset för order där det genomsnittliga försäljningsvärdet är större än $500,00.
Final Statement about HAVING
Även om de utför en liknande funktion, Finns det en viktig skillnad mellan WHERE-klausulen och att ha. WHERE-klausulen för att filtrera enskilda poster. medan ha-klausulen filtrerar på grupperna.
för att hålla det rakt i mitt huvud tycker jag om att tänka på var klausulen gör sitt arbete innan några grupperingar äger rum, och sedan har klausulen tar över efter att grupperna bildas.,
övningar
det är viktigt att träna! Använd provdatabasen för att svara på dessa frågor.
- HR vill ha en rapport om antalet aktiva anställda efter jobbtitel. Vad SQL skulle du använda?
- visa min, Max och genomsnittlig kvantitet som beställts för varje produkt i SalesOrderDetails.
- lista alla anställdas anställningstitlar och antal anställda där det genomsnittliga antalet sjuklediga timmar är mindre än eller lika med fyrtio.
- för en jobbtitel som returneras i # 3 ovan, är räkningen densamma för motsvarande jobbtitel svar # 1 resultat?
svar är här!,
Leave a Reply