SELECT - GROUP BY- Transact-SQL

Van toepassing op:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)SQL Analytics-eindpunt in Microsoft FabricMagazijn in Microsoft FabricSQL-database in Microsoft Fabric Preview

Een SELECT-instructiecomponent waarmee het queryresultaat wordt verdeeld in groepen rijen, meestal door een of meer aggregaties uit te voeren op elke groep. De SELECT-instructie retourneert één rij per groep.

Syntax

Transact-SQL syntaxis-conventies

-- Syntax for SQL Server and Azure SQL Database   
-- ISO-Compliant Syntax  
  
GROUP BY {
      column-expression  
    | ROLLUP ( <group_by_expression> [ ,...n ] )  
    | CUBE ( <group_by_expression> [ ,...n ] )  
    | GROUPING SETS ( <grouping_set> [ ,...n ]  )  
    | () --calculates the grand total 
} [ ,...n ] 
 
<group_by_expression> ::=  
      column-expression  
    | ( column-expression [ ,...n ] )    
   
<grouping_set> ::=  
      () --calculates the grand total  
    | <grouping_set_item>  
    | ( <grouping_set_item> [ ,...n ] )  
  
<grouping_set_item> ::=  
      <group_by_expression>  
    | ROLLUP ( <group_by_expression> [ ,...n ] )  
    | CUBE ( <group_by_expression> [ ,...n ] )  
  

-- For backward compatibility only.
-- Non-ISO-Compliant Syntax for SQL Server and Azure SQL Database 
  
GROUP BY {
       ALL column-expression [ ,...n ] 
    | column-expression [ ,...n ]  WITH { CUBE | ROLLUP }    
       }

-- Syntax for Azure Synapse Analytics 
  
GROUP BY {
      column-name [ WITH (DISTRIBUTED_AGG) ]  
    | column-expression
    | ROLLUP ( <group_by_expression> [ ,...n ] ) 
} [ ,...n ]

-- Syntax for Parallel Data Warehouse  
  
GROUP BY {
      column-name [ WITH (DISTRIBUTED_AGG) ]  
    | column-expression
} [ ,...n ]

Arguments

column-expression

Hiermee geeft u een kolom of een niet-geaggregeerde berekening voor een kolom op. Deze kolom kan behoren tot een tabel, afgeleide tabel of weergave. De kolom moet worden weergegeven in de FROM-component van de SELECT-instructie, maar hoeft niet te worden weergegeven in de SELECT-lijst.

Zie de expressie voor geldige expressies.

De kolom moet worden weergegeven in de FROM-component van de SELECT-instructie, maar hoeft niet te worden weergegeven in de SELECT-lijst. Elke tabel- of weergavekolom in een niet-samengevoegde expressie in de <selectielijst> moet echter worden opgenomen in de GROUP BY-lijst:

De volgende instructies zijn toegestaan:

SELECT ColumnA, ColumnB FROM T GROUP BY ColumnA, ColumnB;  
SELECT ColumnA + ColumnB FROM T GROUP BY ColumnA, ColumnB;  
SELECT ColumnA + ColumnB FROM T GROUP BY ColumnA + ColumnB;  
SELECT ColumnA + ColumnB + constant FROM T GROUP BY ColumnA, ColumnB;  

De volgende instructies zijn niet toegestaan:

SELECT ColumnA, ColumnB FROM T GROUP BY ColumnA + ColumnB;  
SELECT ColumnA + constant + ColumnB FROM T GROUP BY ColumnA + ColumnB;  

De kolomexpressie mag niet het volgende bevatten:

• Een kolomalias die is gedefinieerd in de SELECT-lijst. Het kan een kolomalias gebruiken voor een afgeleide tabel die is gedefinieerd in de FROM-component.
• Een kolom van het type tekst, ntekst of afbeelding. U kunt echter een kolom met tekst, ntext of afbeelding gebruiken als argument voor een functie die een waarde van een geldig gegevenstype retourneert. De expressie kan bijvoorbeeld SUBSTRING() en CAST() gebruiken. Dit geldt ook voor expressies in de HAVING-component.
• methoden voor xml-gegevenstypen. Het kan een door de gebruiker gedefinieerde functie bevatten die gebruikmaakt van methoden voor xml-gegevenstypen. Het kan een berekende kolom bevatten die gebruikmaakt van xml-gegevenstypemethoden.
• A subquery. Fout 144 wordt geretourneerd.
• Een kolom uit een geïndexeerde weergave.

GROUP BY column-expression [ ,... n ]

Groepeert de SELECT-instructieresultaten op basis van de waarden in een lijst met een of meer kolomexpressies.

Met deze query wordt bijvoorbeeld een tabel Sales gemaakt met kolommen voor Land, Regio en Verkoop. Er worden vier rijen ingevoegd en twee van de rijen hebben overeenkomende waarden voor Land en Regio.

CREATE TABLE Sales ( Country VARCHAR(50), Region VARCHAR(50), Sales INT );

INSERT INTO sales VALUES (N'Canada', N'Alberta', 100);
INSERT INTO sales VALUES (N'Canada', N'British Columbia', 200);
INSERT INTO sales VALUES (N'Canada', N'British Columbia', 300);
INSERT INTO sales VALUES (N'United States', N'Montana', 100);

De tabel Sales bevat deze rijen:

Deze volgende query groepeert land en regio en retourneert de geaggregeerde som voor elke combinatie van waarden.

SELECT Country, Region, SUM(sales) AS TotalSales
FROM Sales
GROUP BY Country, Region;

Het queryresultaat heeft drie rijen omdat er drie combinaties van waarden zijn voor Land en Regio. TotalSales voor Canada en Brits-Columbia is de som van twee rijen.

GROEPEREN OP ROLLUP

Hiermee maakt u een groep voor elke combinatie van kolomexpressies. Daarnaast worden de resultaten 'samengerold' in subtotalen en eindtotalen. Hiervoor wordt van rechts naar links verplaatst, waardoor het aantal kolomexpressies waarmee groepen en de aggregatie(s) worden gemaakt, wordt verkleind.

De kolomvolgorde is van invloed op de uitvoer rollUP en kan van invloed zijn op het aantal rijen in de resultatenset.

Maakt bijvoorbeeld GROUP BY ROLLUP (col1, col2, col3, col4) groepen voor elke combinatie van kolomexpressies in de volgende lijsten.

• col1, col2, col3, col4
• col1, col2, col3, NULL
• kolom1, kolom2, NULL, NULL
• col1, NULL, NULL, NULL
• NULL, NULL, NULL, NULL --Dit is het eindtotaal

Met behulp van de tabel uit het vorige voorbeeld wordt met deze code een GROUP BY ROLLUP-bewerking uitgevoerd in plaats van een eenvoudige GROUP BY.

SELECT Country, Region, SUM(Sales) AS TotalSales
FROM Sales
GROUP BY ROLLUP (Country, Region);

Het queryresultaat heeft dezelfde aggregaties als de eenvoudige GROUP BY zonder rollup. Daarnaast worden er subtotalen gemaakt voor elke waarde van Land. Ten slotte geeft het een eindtotaal voor alle rijen. Het resultaat ziet er als volgt uit:

GROUP BY CUBE ( )

GROUP BY CUBE maakt groepen voor alle mogelijke combinaties van kolommen. Voor GROUP BY CUBE (a, b) bevat de resultaten groepen voor unieke waarden (a, b), (NULL, b), (a, NULL) en (NULL, NULL, NULL).

Met behulp van de tabel uit de vorige voorbeelden wordt met deze code een GROUP BY CUBE-bewerking uitgevoerd op Land en Regio.

SELECT Country, Region, SUM(Sales) AS TotalSales
FROM Sales
GROUP BY CUBE (Country, Region);

Het queryresultaat bevat groepen voor unieke waarden (Land, Regio), (NULL, Regio), (Land, NULL) en (NULL, NULL). De resultaten zien er als volgt uit:

GROEPEREN OP GROEPERINGSSETS ()

Met de optie GROEPEREN SETS kunt u meerdere GROUP BY-componenten combineren in één GROUP BY-component. De resultaten zijn het equivalent van UNION ALL van de opgegeven groepen.

U kunt bijvoorbeeld GROUP BY ROLLUP (Country, Region)GROUP BY GROUPING SETS ( ROLLUP (Country, Region) ) dezelfde resultaten retourneren.

Wanneer GROUPING SETS twee of meer elementen heeft, zijn de resultaten een samenvoeging van de elementen. In dit voorbeeld wordt de samenvoeging van de resultaten ROLLUP en KUBUS geretourneerd voor Land en Regio.

SELECT Country, Region, SUM(Sales) AS TotalSales
FROM Sales
GROUP BY GROUPING SETS ( ROLLUP (Country, Region), CUBE (Country, Region) );

De resultaten zijn hetzelfde als deze query die een samenvoeging van de twee GROUP BY-instructies retourneert.

SELECT Country, Region, SUM(Sales) AS TotalSales
FROM Sales
GROUP BY ROLLUP (Country, Region)
UNION ALL
SELECT Country, Region, SUM(Sales) AS TotalSales
FROM Sales
GROUP BY CUBE (Country, Region);

IN SQL worden geen dubbele groepen geconsolideerd die zijn gegenereerd voor een lijst MET GROEPERINGSSETs. In beide elementen wordt bijvoorbeeld GROUP BY ( (), CUBE (Country, Region) )een rij voor het eindtotaal geretourneerd en worden beide rijen weergegeven in de resultaten.

GROUP BY ()

Hiermee geeft u de lege groep op, waarmee het eindtotaal wordt gegenereerd. Dit is handig als een van de elementen van een GROEPEERSET. Deze instructie geeft bijvoorbeeld de totale verkoop voor elk land/elke regio en geeft vervolgens het eindtotaal voor alle landen/regio's.

SELECT Country, SUM(Sales) AS TotalSales
FROM Sales
GROUP BY GROUPING SETS ( Country, () );

GROUP BY ALL column-expression [ ,... n ]

Van toepassing op: SQL Server en Azure SQL Database

Hiermee geeft u alle groepen op in de resultaten, ongeacht of ze voldoen aan de zoekcriteria in de WHERE-component. Groepen die niet voldoen aan de zoekcriteria hebben NULL voor de aggregatie.

GROEPEREN OP ALLES:

• Wordt niet ondersteund in query's die toegang hebben tot externe tabellen als er ook een WHERE-component in de query is.
• Mislukt voor kolommen met het kenmerk FILESTREAM.

GROUP BY column-expression [ ,... n ] MET { KUBUS | ROLLUP }

Van toepassing op: SQL Server en Azure SQL Database

WITH (DISTRIBUTED_AGG)

Van toepassing op: Azure Synapse Analytics and Analytics Platform System (PDW)

De DISTRIBUTED_AGG queryhint dwingt het MPP-systeem (Massively Parallel Processing) om een tabel op een specifieke kolom opnieuw te distribueren voordat een aggregatie wordt uitgevoerd. Slechts één kolom in de GROUP BY-component kan een DISTRIBUTED_AGG queryhint hebben. Nadat de query is voltooid, wordt de opnieuw gedistribueerde tabel verwijderd. De oorspronkelijke tabel wordt niet gewijzigd.

OPMERKING: De DISTRIBUTED_AGG queryhint wordt geleverd voor achterwaartse compatibiliteit met eerdere versies van Analytics Platform System (PDW) en zal de prestaties voor de meeste query's niet verbeteren. Standaard worden met MPP al gegevens herdistribueerd als dat nodig is om de prestaties voor aggregaties te verbeteren.

General Remarks

Hoe GROUP BY communiceert met de SELECT-instructie

SELECT list:

• Vector aggregates. Als statistische functies zijn opgenomen in de SELECT-lijst, berekent GROUP BY een samenvattingswaarde voor elke groep. Deze worden vectoraggregaties genoemd.
• Distinct aggregates. De aggregaties AVG (DISTINCT column_name), COUNT (DISTINCT column_name) en SUM (DISTINCT column_name) worden ondersteund met ROLLUP-, KUBUS- en GROEPERINGSSETS.

WHERE clause:

• SQL verwijdert rijen die niet voldoen aan de voorwaarden in de WHERE-component voordat een groeperingsbewerking wordt uitgevoerd.

HAVING clause:

• SQL gebruikt de having-component om groepen in de resultatenset te filteren.

ORDER BY-component:

• Gebruik de COMPONENT ORDER BY om de resultatenset te orden. De GROUP BY-component rangschikt de resultatenset niet.

NULL values:

• Als een groepeerkolom NULL-waarden bevat, worden alle NULL-waarden als gelijk beschouwd en worden ze verzameld in één groep.

Beperkingen en beperkingen

Van toepassing op: SQL Server (vanaf 2008) en Azure Synapse Analytics

Maximum capacity

Voor een GROUP BY-component die GEBRUIKMAAKT van ROLLUP-, KUBUS- of GROEPERINGSSETS, is het maximum aantal expressies 32. Het maximum aantal groepen is 4096 (2¹²). De volgende voorbeelden mislukken omdat de GROUP BY-component meer dan 4096 groepen heeft.

• In het volgende voorbeeld worden 4097 (2¹² + 1) groeperingssets gegenereerd en mislukt het.

  GROUP BY GROUPING SETS( CUBE(a1, ..., a12), b )  
  

• In het volgende voorbeeld worden 4097 (2¹² + 1) groepen gegenereerd en mislukt het. Zowel CUBE () als de () groeperingsset produceren een eindtotaalrij en dubbele groeperingssets worden niet geëlimineerd.

  GROUP BY GROUPING SETS( CUBE(a1, ..., a12), ())  
  

• In dit voorbeeld wordt de achterwaarts compatibele syntaxis gebruikt. Er worden 8192 (2¹³) groeperingssets gegenereerd en mislukt.

  GROUP BY CUBE (a1, ..., a13)   
  GROUP BY a1, ..., a13 WITH CUBE   
  

  Voor achterwaarts compatibele GROUP BY-componenten die geen KUBUS of ROLLUP bevatten, wordt het aantal groeperen op items beperkt door de kolomgrootten GROUP BY, de samengevoegde kolommen en de cumulatieve waarden die bij de query betrokken zijn. Deze limiet is afkomstig van de limiet van 8060 bytes op de tussenliggende werktabel die nodig is voor het opslaan van tussenliggende queryresultaten. Er zijn maximaal 12 groeperingsexpressies toegestaan wanneer KUBUS of ROLLUP is opgegeven.

Ondersteuning voor ISO- en ANSI SQL-2006 GROUP BY-functies

De GROUP BY-component ondersteunt alle GROUP BY-functies die zijn opgenomen in de SQL-2006-standaard met de volgende syntaxisuitzonderingen:

• Groeperingssets zijn niet toegestaan in de GROUP BY-component, tenzij ze deel uitmaken van een expliciete lijst GROEPEERSETs. Bijvoorbeeld: GROUP BY Column1, (Column2, ...ColumnN) is toegestaan in de standaard, maar niet in Transact-SQL. Transact-SQL ondersteunt GROUP BY C1, GROUPING SETS ((Column2, ...ColumnN)) en GROUP BY Column1, Column2, ... ColumnN, die semantisch gelijkwaardig zijn. Deze zijn semantisch gelijk aan het vorige GROUP BY voorbeeld. Dit is om te voorkomen dat GROUP BY Column1, (Column2, ...ColumnN) verkeerd kan worden geïnterpreteerd als GROUP BY C1, GROUPING SETS ((Column2, ...ColumnN)), die niet semantisch gelijkwaardig zijn.

• Groeperingssets zijn niet toegestaan in groepeersets. Is bijvoorbeeld GROUP BY GROUPING SETS (A1, A2,...An, GROUPING SETS (C1, C2, ...Cn)) toegestaan in de SQL-2006-standaard, maar niet in Transact-SQL. Transact-SQL staat GROUP BY GROUPING SETS( A1, A2,...An, C1, C2, ...Cn ) toe of GROUP BY GROUPING SETS( (A1), (A2), ... (An), (C1), (C2), ... (Cn) ), die semantisch gelijk zijn aan het eerste GROUP BY-voorbeeld en hebben een duidelijkere syntaxis.

• GROUP BY [ALL/DISTINCT] is alleen toegestaan in een eenvoudige GROUP BY-component die kolomexpressies bevat. Het is niet toegestaan met de GROEPERINGSSETS, ROLLUP, KUBUS, WITH CUBE of WITH ROLLUP-constructies. ALL is de standaardinstelling en is impliciet. Het is ook alleen toegestaan in de achterwaarts compatibele syntaxis.

Vergelijking van ondersteunde GROUP BY-functies

In de volgende tabel worden de GROUP BY-functies beschreven die worden ondersteund op basis van sql-versies en databasecompatibiliteitsniveau.

Examples

A. Een eenvoudige GROUP BY-component gebruiken

In het volgende voorbeeld wordt het totaal voor elk SalesOrderID uit de SalesOrderDetail tabel opgehaald. In dit voorbeeld wordt AdventureWorks gebruikt.

SELECT SalesOrderID, SUM(LineTotal) AS SubTotal  
FROM Sales.SalesOrderDetail AS sod  
GROUP BY SalesOrderID  
ORDER BY SalesOrderID;  

B. Een GROUP BY-component gebruiken met meerdere tabellen

In het volgende voorbeeld wordt het aantal werknemers voor elke City werknemer opgehaald uit de Address tabel die aan de EmployeeAddress tabel is gekoppeld. In dit voorbeeld wordt AdventureWorks gebruikt.

SELECT a.City, COUNT(bea.AddressID) EmployeeCount  
FROM Person.BusinessEntityAddress AS bea   
    INNER JOIN Person.Address AS a  
        ON bea.AddressID = a.AddressID  
GROUP BY a.City  
ORDER BY a.City;  

C. Een GROUP BY-component gebruiken met een expressie

In het volgende voorbeeld wordt de totale verkoop voor elk jaar opgehaald met behulp van de DATEPART functie. Dezelfde expressie moet aanwezig zijn in zowel de lijst als GROUP BY de SELECT component.

SELECT DATEPART(yyyy,OrderDate) AS N'Year'  
    ,SUM(TotalDue) AS N'Total Order Amount'  
FROM Sales.SalesOrderHeader  
GROUP BY DATEPART(yyyy,OrderDate)  
ORDER BY DATEPART(yyyy,OrderDate);  

D. Een GROUP BY-component gebruiken met een HAVING-component

In het volgende voorbeeld wordt de HAVING component gebruikt om op te geven welke van de groepen die in de GROUP BY component zijn gegenereerd, moeten worden opgenomen in de resultatenset.

SELECT DATEPART(yyyy,OrderDate) AS N'Year'  
    ,SUM(TotalDue) AS N'Total Order Amount'  
FROM Sales.SalesOrderHeader  
GROUP BY DATEPART(yyyy,OrderDate)  
HAVING DATEPART(yyyy,OrderDate) >= N'2003'  
ORDER BY DATEPART(yyyy,OrderDate);  

Voorbeelden: Azure Synapse Analytics en Parallel Data Warehouse

E. Basisgebruik van de GROUP BY-component

In het volgende voorbeeld wordt het totale bedrag voor alle verkopen per dag gevonden. Eén rij met de som van alle verkopen wordt elke dag geretourneerd.

-- Uses AdventureWorksDW  
  
SELECT OrderDateKey, SUM(SalesAmount) AS TotalSales FROM FactInternetSales  
GROUP BY OrderDateKey ORDER BY OrderDateKey;  

F. Basisgebruik van de DISTRIBUTED_AGG hint

In dit voorbeeld wordt de DISTRIBUTED_AGG queryhint gebruikt om het apparaat te dwingen de tabel in de CustomerKey kolom te schuiven voordat de aggregatie wordt uitgevoerd.

-- Uses AdventureWorksDW  
  
SELECT CustomerKey, SUM(SalesAmount) AS sas  
FROM FactInternetSales  
GROUP BY CustomerKey WITH (DISTRIBUTED_AGG)  
ORDER BY CustomerKey DESC;  

G. Syntaxisvariaties voor GROUP BY

Wanneer de selectielijst geen aggregaties heeft, moet elke kolom in de selectielijst worden opgenomen in de GROUP BY-lijst. Berekende kolommen in de selectielijst kunnen worden weergegeven, maar zijn niet vereist, in de lijst GROUP BY. Dit zijn voorbeelden van syntactisch geldige SELECT-instructies:

-- Uses AdventureWorks  
  
SELECT LastName, FirstName FROM DimCustomer GROUP BY LastName, FirstName;  
SELECT NumberCarsOwned FROM DimCustomer GROUP BY YearlyIncome, NumberCarsOwned;  
SELECT (SalesAmount + TaxAmt + Freight) AS TotalCost FROM FactInternetSales GROUP BY SalesAmount, TaxAmt, Freight;  
SELECT SalesAmount, SalesAmount*1.10 SalesTax FROM FactInternetSales GROUP BY SalesAmount;  
SELECT SalesAmount FROM FactInternetSales GROUP BY SalesAmount, SalesAmount*1.10;  

H. Een GROUP BY gebruiken met meerdere GROUP BY-expressies

In het volgende voorbeeld worden resultaten gegroepeerd met behulp van meerdere GROUP BY criteria. Als er binnen elke OrderDateKey groep subgroepen zijn die kunnen worden onderscheiden DueDateKeydoor, wordt een nieuwe groepering gedefinieerd voor de resultatenset.

-- Uses AdventureWorks  
  
SELECT OrderDateKey, DueDateKey, SUM(SalesAmount) AS TotalSales   
FROM FactInternetSales
GROUP BY OrderDateKey, DueDateKey   
ORDER BY OrderDateKey;  

I. Een GROUP BY-component gebruiken met een HAVING-component

In het volgende voorbeeld wordt de HAVING component gebruikt om de groepen op te geven die zijn gegenereerd in de GROUP BY component die moeten worden opgenomen in de resultatenset. Alleen groepen met orderdatums in 2004 of hoger worden opgenomen in de resultaten.

-- Uses AdventureWorks  
  
SELECT OrderDateKey, SUM(SalesAmount) AS TotalSales   
FROM FactInternetSales  
GROUP BY OrderDateKey   
HAVING OrderDateKey > 20040000   
ORDER BY OrderDateKey;  

See Also

GROUPING_ID (Transact-SQL)
GROUPING (Transact-SQL)
SELECT (Transact-SQL)
SELECT-clausule (Transact-SQL)