Rezumați rezultatele SQL cu clauza grup după

în lecția de astăzi, veți afla despre gruparea rezultatelor returnate din interogările dvs. folosind clauza grup după SQL.obiectivele lecției de astăzi sunt:

• Aflați cum să grupați rezultatele folosind GROUP BY
• utilizați funcțiile agregate pentru a efectua calcule
• înțelegeți cum să filtrați grupurile folosind clauza HAVING

Important! Vă rugăm să urmați de-a lungul și de a face exemplele din Baza de date. Dacă nu ați făcut-o deja, înscrieți-vă la ghidul meu pentru a începe cu SQL Server., Obțineți instrucțiuni despre cum să instalați instrumentele gratuite și baza de date de mostre.

SQL GROUP by Clauză

SQL GROUP BY clauza este utilizată pentru a afișa un rând între valorile specificate ale coloanelor. Acesta este de obicei utilizat în combinație cu funcții agregate, cum ar fi suma sau conta pentru a rezuma valorile. În grupurile SQL sunt combinații unice de câmpuri. În loc să returneze fiecare rând dintr-un tabel, atunci când valorile sunt grupate, numai combinațiile unice sunt returnate.

grupul de clauza se adaugă la instrucțiunea SQL după clauza WHERE., Iată un exemplu în care listăm SalesOrderID, excluzând cantitățile mai mari de 100.

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

Există câteva lucruri de reținut. În primul rând, coloanele pe care dorim să le rezumăm sunt listate, separate prin virgule, în grupul după clauză. În al doilea rând, aceeași listă de coloane trebuie să fie listate în instrucțiunea select; altfel instrucțiunea eșuează.,

când această instrucțiune este rulată, nu fiecare rând filtrat este returnat. Numai combinații unice de SalesOrderID sunt incluse în rezultat. Această declarație este foarte similar cu

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

Dar există o diferență esențială., Modificatorul DISTINCT se oprește la afișarea unei combinații unice de rânduri, în timp ce, cu grupul după instrucțiune, putem calcula valori pe baza rândurilor filtrate subiacente pentru fiecare combinație unică.

cu alte cuvinte, folosind exemplul nostru, cu GRUPUL DE, putem calcula numărul sau OrderDetails pe comanda după cum urmează:

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

CONTELE este un exemplu de o funcție agregată, acestea sunt ceea ce da într-adevăr GRUP DE declarație valoarea sa deosebită.unele funcții, cum ar fi SUM, sunt utilizate pentru a efectua calcule pe un grup de rânduri, acestea fiind numite funcții agregate. În cele mai multe cazuri, aceste funcții funcționează pe un grup de valori care sunt definite folosind clauza GROUP BY., Atunci când nu există un grup de clauză, se înțelege, în general, funcția agregată se aplică tuturor rezultatelor filtrate.

Unele dintre cele mai comune funcții agregate include:

Aceste funcții pot fi utilizate pe cont propriu în legătură cu clauza GROUP BY. Pe cont propriu, ele funcționează pe întregul tabel; cu toate acestea, atunci când sunt utilizate cu GROUP BY, calculele lor sunt „resetate” de fiecare dată când gruparea se schimbă., În acest mod, ele acționează ca subtotaluri.

funcție agregată simplă

când utilizați funcția agregată, puteți calcula rezultatul pe toate valorile sau valorile distincte. De exemplu, pentru a număra toate SalesOrderDetails înregistrări putem folosi expresia:

SELECT COUNT(SalesOrderID)
FROM Sales.SalesOrderDetail

Acest lucru duce la un număr de 121317 fi returnate.

Să numere distincte de ordine de a face până la detalii ne-ar folosi la următoarele:

SELECT COUNT(Distinct SalesOrderID)
FROM Sales.SalesOrderDetail

conta acum este 31465.,

SQL GROUP BY and Aggregate Functions

To aggregate means to make whole from individual parts. Funcțiile agregate sunt funcții care funcționează pe mai mult de un rând pentru a returna un rezultat.

AVG și SUM

funcția SUM totalizează valorile returnate, în mod similar AVG calculează media.să vedem dacă putem calcula suma totală a Comenzii din Comandădetalii., Din lecțiile anterioare că știu cum să calculeze suma totală pentru fiecare detaliu ca:

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

Deoarece putem aplica funcția agregată de expresii, putem stabili o grupare pe OrderID pentru a calcula prețul total pe comanda ca

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

putem chiar un fel de total pentru a obține primele comenzi în primul rând

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

În mod similar putem calcula media pentru detaliu, folosind AVG. De ce încercați mai jos! Dacă rămâneți blocat comuta răspunsul.

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

Pentru curiosi, deoarece o medie este calculată ca sumă a probei împărțită la numărul de probă, apoi, folosind AVG, în declarația de mai sus este aceeași ca:

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

Am acoperit o mulțime în această secțiune., Aici sunt câteva puncte cheie de reținut:

1. O funcție agregată poate evalua o expresie, cum ar fi SUMA(A + B)
2. ar trebui să alias funcții agregate, astfel încât coloana nume sunt semnificative
3. atunci Când se lucrează cu funcții agregate și de GRUP, este, uneori, este mai ușor să se gândească la detalii, în primul rând, că este scris de un simplu SELECT, verificați rezultatele, apoi se adaugă în lucruri de lux. Am acoperi acest lucru în modul de a scrie interogări mai bune.

SQL COUNT

funcția COUNT este utilizată atunci când trebuie să știți câte înregistrări există într-un tabel sau într-un grup., COUNT ( * ) va număra fiecare înregistrare din grup; în timp ce COUNT(expression) numără fiecare înregistrare unde rezultatul expresiei nu este nul. De asemenea, puteți utiliza Distinct cu COUNT pentru a găsi numărul de valori unice dintr-un grup.

Pentru a găsi numărul de SalesOrderDetail Linii pe comanda

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

Pentru a găsi numărul unic de comenzi de vânzări pe produs

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

MIN și MAX

Utilizarea MIN și MAX pentru a gasi cele mai mici și cele mai mari valori, respectiv, în termen de o masă sau de grup.,de exemplu, pentru a găsi cele mai mici și mai mari cantități de produse comandate într-o comandă, încercați

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

puteți găsi, de asemenea, valoarea MIN sau MAX a unui calcul. Aici găsim cea mai mare sumă de produse comandate în termen de un produs:

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

AVÂND Clauza

SQL AVÂND clauza este folosit pentru a filtra grupuri, în funcție de rezultatele funcții agregate. Acest lucru face posibilă rezolvarea problemelor, cum ar fi selectarea tuturor comenzilor care au mai mult de două linii de detaliu ale comenzii.,

acest exemplu arata ca

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

Dacă am vrut să găsesc toate comenzile mai mari decât 1000 de dolari ne-ar scrie

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

Rețineți că putem folosi alias TotalPrice în clauza ORDER BY, dar, apoi, având o clauză a folosi expresia.

pentru a hammer acasă având, vreau să arăt un ultim exemplu. Aici veți vedea că instrucțiunea HAVING include o funcție agregată care nu se află în lista SELECT.,

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

În interogarea de mai sus vom obține prețul total pentru comenzi în cazul în care media SalesOrderDetail suma este mai mare de $500.00.

declarație finală despre a avea

deși îndeplinesc o funcție similară, există o distincție cheie între clauza WHERE și HAVING. Clauza WHERE pentru a filtra înregistrările individuale; întrucât clauza HAVING filtrează grupurile.

pentru a o păstra drept în capul meu, îmi place să mă gândesc la clauza WHERE care își desfășoară activitatea înainte de a avea loc orice grupări și apoi la clauza HAVING care preia după formarea grupurilor.,

exerciții

este important să practicați! Utilizați baza de date eșantion pentru a răspunde la aceste întrebări.

1. HR dorește un raport al numărului de angajați activi după titlul postului. Ce SQL ai folosi?
2. afișează Min, Max, și cantitatea medie comandată pentru fiecare produs în detalii SalesOrderDetails.
3. enumerați toate titlurile de locuri de muncă ale angajaților și numărul de angajați în care numărul mediu de ore de concediu medical este mai mic sau egal cu patruzeci.
4. pentru un titlu de job returnat în #3 de mai sus, numărul este același pentru răspunsul corespunzător la titlul jobului # 1 ‘ s result ?

răspunsurile sunt aici!,