Gegevens samenvatten met GROUP BY
Hoewel statistische functies nuttig zijn voor analyse, kunt u uw gegevens rangschikken in subsets voordat u deze samenvat. In deze sectie leert u hoe u dit kunt doen met behulp van de GROUP BY-component.
De GROUP BY-component gebruiken
Zoals u hebt geleerd, wordt er, nadat de FROM-component en WHERE-component zijn geëvalueerd, een virtuele tabel gemaakt wanneer uw SELECT-instructie wordt verwerkt. De inhoud van de virtuele tabel is nu beschikbaar voor verdere verwerking. U kunt de GROUP BY-component gebruiken om de inhoud van deze virtuele tabel te verdelen in groepen rijen.
Als u rijen wilt groeperen, geeft u een of meer elementen op in de GROUP BY-component:
GROUP BY <value1> [, <value2>, …]
GROUP BY maakt groepen en plaatst rijen in elke groep, zoals wordt bepaald door de elementen die zijn opgegeven in de component.
De volgende query resulteert bijvoorbeeld in een set gegroepeerde rijen, één rij per CustomerID in de tabel Sales.SalesOrderHeader . Een andere manier om naar het GROUP BY-proces te kijken, is dat alle rijen met dezelfde waarde voor CustomerID worden gegroepeerd en in één resultaatrij worden geretourneerd.
SELECT CustomerID
FROM Sales.SalesOrderHeader
GROUP BY CustomerID;
De bovenstaande query is gelijk aan de volgende query:
SELECT DISTINCT CustomerID
FROM Sales.SalesOrderHeader
Nadat de GROUP BY-component is verwerkt en elke rij is gekoppeld aan een groep, moeten latere fasen van de query alle elementen van de bronrijen in de SELECT-lijst aggregeren, maar die niet worden weergegeven in de lijst GROUP BY. Deze vereiste heeft invloed op de wijze waarop u uw SELECT- en HAVING-componenten schrijft.
Wat is het verschil tussen het schrijven van de query met een GROUP BY of een DISTINCT? Als alles wat u wilt weten de unieke waarden voor CustomerID is, is er geen verschil. Maar met GROUP BY kunnen we andere elementen toevoegen aan de SELECT-lijst die vervolgens voor elke groep worden samengevoegd.
De eenvoudigste statistische functie is COUNT(*). De volgende query maakt gebruik van de oorspronkelijke 830 bronrijen uit CustomerID en groepeert deze in 89 groepen, op basis van de customerID-waarden . Elke afzonderlijke CustomerID-waarde genereert één rij met uitvoer in de GROUP BY-query
SELECT CustomerID, COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY CustomerID;
Voor elke CustomerID-waarde worden de rijen samengevoegd en geteld, dus het resultaat laat zien hoeveel rijen in de tabel SalesOrderHeader bij elke klant horen.
KlantID
AantalBestellingen
1234
3
1005
1
Group BY garandeert niet de volgorde van de resultaten. Als gevolg van de manier waarop de groeperingsbewerking wordt uitgevoerd door de queryprocessor, worden de resultaten vaak geretourneerd in de volgorde van de groepswaarden. U moet echter niet vertrouwen op dit gedrag. Als u de resultaten wilt sorteren, moet u expliciet een ORDER-component opnemen:
SELECT CustomerID, COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY CustomerID
ORDER BY CustomerID;
Deze keer worden de resultaten geretourneerd in de opgegeven volgorde:
KlantID
AantalBestellingen
1005
1
1234
3
De componenten in een SELECT-instructie worden in de volgende volgorde toegepast:
- VAN
- WAAR
- GROEP OP
- HEBBEN
- SELECTEREN
- SORTEER OP
Kolomaliassen worden toegewezen in de SELECT-clausule, die plaatsvindt na de GROUP BY-clausule, maar vóór de ORDER BY-clausule. U kunt verwijzen naar een kolomalias in de ORDER BY-component, maar niet in de GROUP BY-component. De volgende query resulteert in een ongeldige kolomnaamfout :
SELECT CustomerID AS Customer,
COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY Customer
ORDER BY Customer;
De volgende query zal echter slagen en de resultaten groeperen en sorteren op basis van de klant-ID.
SELECT CustomerID AS Customer,
COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY CustomerID
ORDER BY Customer;
Problemen met GROUP BY-fouten oplossen
Een veelvoorkomend obstakel voor het gebruik van GROUP BY in SELECT-instructies is het begrijpen waarom het volgende type foutbericht optreedt:
Msg 8120, Niveau 16, Status 1, Regel 2 kolom <column_name> is ongeldig in de selectielijst omdat deze niet is opgenomen in een aggregatiefunctie of de GROUP BY-clause.
De volgende query is bijvoorbeeld toegestaan omdat elke kolom in de SELECT-lijst een kolom is in de GROUP BY-component of een statistische functie die op elke groep wordt uitgevoerd:
SELECT CustomerID, COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY CustomerID;
De volgende query retourneert een fout omdat PurchaseOrderNumber geen deel uitmaakt van group by en niet wordt gebruikt met een statistische functie.
SELECT CustomerID, PurchaseOrderNumber, COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY CustomerID;
Deze query geeft de fout:
Msg 8120, Level 16, State 1, Line 1
Column 'Sales.SalesOrderHeader.PurchaseOrderNumber' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Hier is een andere manier om erover na te denken. Deze query retourneert één rij voor elke CustomerID-waarde . Maar rijen voor dezelfde CustomerID kunnen verschillende PurchaseOrderNumber-waarden hebben, dus welke van de waarden moet worden geretourneerd?
Als u orders per klant-id en per inkooporder wilt zien, kunt u de kolom PurchaseOrderNumber als volgt toevoegen aan de GROUP BY-component:
SELECT CustomerID, PurchaseOrderNumber, COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY CustomerID, PurchaseOrderNumber;
Deze query retourneert één rij voor elke klant en elke combinatie van inkooporders, samen met het aantal orders voor die combinatie.