GROUP BY (Transact-SQL)

Fasst eine ausgewählte Menge von Zeilen entsprechend den Werten einer oder mehrerer Spalten oder Ausdrücke in SQL Server 2012 zu einer Gruppe von Zusammenfassungszeilen zusammen. Für jede Gruppe wird eine Zeile zurückgegeben. Aggregatfunktionen in der <select>-Liste der SELECT-Klausel stellen Informationen über jede Gruppe statt einzelner Zeilen bereit.

Die GROUP BY-Klausel verfügt über eine ISO-kompatible Syntax und eine nicht ISO-kompatible Syntax. In einer einzelnen SELECT-Anweisung kann nur ein Syntaxformat verwendet werden. Verwenden Sie künftig nur noch die ISO-kompatible Syntax. Die nicht ISO-kompatible Syntax wird lediglich aus Gründen der Abwärtskompatibilität bereitgestellt.

In diesem Thema kann eine GROUP BY-Klausel als allgemein oder als einfach beschrieben werden:

• Eine allgemeine GROUP BY-Klausel schließt GROUPING SETS, CUBE, ROLLUP, WITH CUBE oder WITH ROLLUP ein.

• Eine einfache GROUP BY-Klausel schließt GROUPING SETS, CUBE, ROLLUP, WITH CUBE oder WITH ROLLUP nicht ein. GROUP BY () (Gesamtergebnis) wird als einfache GROUP BY-Klausel betrachtet.

Transact-SQL-Syntaxkonventionen (Transact-SQL)

Syntax

ISO-Compliant Syntax

GROUP BY <group by spec>

<group by spec> ::=
    <group by item> [ ,...n ]

<group by item> ::=
    <simple group by item>
    | <rollup spec>
    | <cube spec>
    | <grouping sets spec>
    | <grand total>

<simple group by item> ::=
    <column_expression>

<rollup spec> ::=
    ROLLUP ( <composite element list> ) 

<cube spec> ::=
    CUBE ( <composite element list> ) 

<composite element list> ::=
    <composite element> [ ,...n ]

<composite element> ::=
    <simple group by item>
    | ( <simple group by item list> ) 

<simple group by item list> ::=
    <simple group by item> [ ,...n ]

<grouping sets spec> ::=
    GROUPING SETS ( <grouping set list> ) 

<grouping set list> ::=
    <grouping set> [ ,...n ]

<grouping set> ::=
    <grand total>
    | <grouping set item>
    | ( <grouping set item list> ) 

<empty group> ::= 
        ( ) 

<grouping set item> ::=
    <simple group by item>
    | <rollup spec>
    | <cube spec>

<grouping set item list> ::=
    <grouping set item> [ ,...n ]

Non-ISO-Compliant Syntax
[ GROUP BY [ ALL ] group_by_expression [ ,...n ]
    [ WITH { CUBE | ROLLUP } ] 
]

Argumente

• <column_expression>
  Der Ausdruck, für den der Gruppierungsvorgang ausgeführt wird.

• ROLLUP ( )
  Generiert einfache GROUP BY-Aggregatzeilen, zusammen mit Zwischensummen- oder Superaggregatzeilen sowie auch eine Gesamtergebniszeile.

  Die Anzahl der zurückgegebenen Gruppierungen entspricht der Anzahl der Ausdrücke in der <Liste zusammengesetzter Elemente> plus einer Gruppierung. Sehen Sie sich beispielsweise folgende Anweisung an.

  SELECT a, b, c, SUM ( <expression> )
  FROM T
  GROUP BY ROLLUP (a,b,c);
  

  Eine Zeile mit einer Zwischensumme wird für jede eindeutige Kombination der Werte von (a, b, c), (a, b) und (a) generiert. Außerdem wird eine Gesamtergebniszeile berechnet.

  Das Rollup der Spalten wird von rechts nach links ausgeführt. Die Reihenfolge der Spalten wirkt sich auf die Ausgabegruppierungen von ROLLUP und möglicherweise auch auf die Anzahl der Zeilen im Resultset aus.

• CUBE ( )
  Generiert einfache GROUP BY-Aggregatzeilen, die ROLLUP-Superaggregatzeilen und Kreuztabellenzeilen.

  CUBE gibt eine Gruppierung für alle Permutationen der Ausdrücke in der <Liste zusammengesetzter Elemente> aus.

  Die Anzahl der generierten Gruppierungen entspricht (2n), wobei n = Anzahl der Ausdrücke in der <Liste zusammengesetzter Elemente>. Sehen Sie sich beispielsweise folgende Anweisung an.

  SELECT a, b, c, SUM (<expression>)
  FROM T
  GROUP BY CUBE (a,b,c);
  

  Für jede eindeutige Kombination der Werte von (a, b, c), (a, b), (a, c), (b, c), (a), (b) und (c) wird eine Zeile generiert, zusammen mit einer Zwischensumme für jede Zeile sowie eine Gesamtergebniszeile.

  Die Reihenfolge der Spalten wirkt sich nicht auf die Ausgabe von CUBE aus.

• GROUPING SETS ( )
  Gibt mehrere Gruppierungen der Daten in einer Abfrage an. Es werden statt der vollständigen Menge der Aggregationen, die von CUBE oder ROLLUP generiert werden, nur die angegebenen Gruppen aggregiert. Die Ergebnisse entsprechen denen von UNION ALL für die angegebenen Gruppen. GROUPING SETS kann ein einzelnes Element oder eine Liste von Elementen enthalten. GROUPING SETS kann Gruppierungen angeben, die jenen entsprechen, die von ROLLUP oder CUBE zurückgegeben werden. <grouping set item list> kann ROLLUP oder CUBE enthalten.

• ( )
  Die leere Gruppe generiert eine Gesamtsumme.

Nicht ISO-kompatible Syntax

• ALL
  Diese Funktion wird in zukünftigen Versionen von Microsoft SQL Server nicht mehr bereitgestellt. Verwenden Sie diese Funktion beim Entwickeln neuer Anwendungen nicht, und planen Sie das Ändern von Anwendungen, in denen es zurzeit verwendet wird. Bezieht alle Gruppen und Resultsets ein, auch solche, die keine Zeilen enthalten, die die in der WHERE-Klausel angegebenen Bedingungen erfüllen. Wenn ALL angegeben wird, werden NULL-Werte für die Summenspalten der Gruppen zurückgegeben, die die Suchbedingung nicht erfüllen. ALL kann nicht mit den Operatoren CUBE oder ROLLUP angegeben werden.

  GROUP BY ALL wird für Abfragen nicht unterstützt, die auf Remotetabellen zugreifen und eine WHERE-Klausel enthalten. GROUP BY ALL scheitert bei Spalten, die über das FILESTREAM-Attribut verfügen.

• group_by_expression
  Ein Ausdruck, für den die Gruppierung ausgeführt wird. group_by_expression wird auch als Gruppierungsspalte bezeichnet. Bei group_by expression kann es sich um Spalten oder Nichtaggregatausdrücke handeln, die auf eine Spalte verweisen, die von der FROM-Klausel zurückgegeben wurde. Ein in der SELECT-Liste definierter Spaltenalias kann nicht dafür verwendet werden, eine Gruppierungsspalte anzugeben.

  Hinweis
  Spalten vom Typ text, ntext und image können in group_by_expression nicht verwendet werden.

  Bei GROUP BY-Klauseln, die weder CUBE noch ROLLUP enthalten, ist die Anzahl von group_by_expression-Elementen durch die GROUP BY-Spaltengrößen, die Aggregatspalten und die Aggregatwerte der Abfrage beschränkt. Diese Beschränkung ergibt sich aus der maximalen Größe von 8.060 Byte der temporären Arbeitstabelle, die für die zwischenzeitlich erstellten Abfrageergebnisse benötigt wird. Bei Angabe von CUBE oder ROLLUP sind höchstens 12 Gruppierungsausdrücke zulässig.

  Methoden für xml-Datentypen können nicht direkt in group_by_expression angegeben werden. Verweisen Sie stattdessen auf eine benutzerdefinierte Funktion, die darin enthaltene xml-Datentypmethoden verwendet, oder verweisen Sie auf eine berechnete Spalte, die sie verwendet.

• WITH CUBE
  Diese Funktion wird in zukünftigen Versionen von Microsoft SQL Server nicht mehr bereitgestellt. Verwenden Sie diese Funktion beim Entwickeln neuer Anwendungen nicht, und planen Sie das Ändern von Anwendungen, in denen es zurzeit verwendet wird. Gibt an, dass zusätzlich zu den normalerweise von GROUP BY bereitgestellten Zeilen auch Zusammenfassungszeilen in das Resultset aufgenommen werden. Eine GROUP BY-Summenzeile wird für jede mögliche Kombination von Gruppen und Untergruppen im Resultset zurückgegeben. Verwenden Sie die GROUPING-Funktion, um zu ermitteln, ob alle NULL-Werte im Resultset GROUP BY-Summenwerte sind.

  Die Anzahl von Summenzeilen im Resultset richtet sich nach der Anzahl von Spalten, die in der GROUP BY-Klausel eingeschlossen sind. Da CUBE jede mögliche Kombination aus Gruppen und Untergruppen zurückgibt, ist die Anzahl von Zeilen unabhängig von der Reihenfolge, in der die Gruppierungsspalten festgelegt werden, immer gleich.

• WITH ROLLUP
  Diese Funktion wird in zukünftigen Versionen von Microsoft SQL Server nicht mehr bereitgestellt. Verwenden Sie diese Funktion beim Entwickeln neuer Anwendungen nicht, und planen Sie das Ändern von Anwendungen, in denen es zurzeit verwendet wird. Gibt an, dass zusätzlich zu den normalerweise von GROUP BY bereitgestellten Zeilen auch Zusammenfassungszeilen in das Resultset aufgenommen werden. Gruppen werden in hierarchischer Reihenfolge von der niedrigsten Ebene in der Gruppe bis zur höchsten Ebene zusammengefasst. Die Gruppenhierarchie wird durch die Reihenfolge bestimmt, in der die Gruppierungsspalten angegeben werden. Das Ändern der Reihenfolge der Gruppierungsspalten kann sich auf die Anzahl von im Resultset erstellten Zeilen auswirken.

  Wichtig
  DISTINCT-Aggregate, wie z. B. AVG (DISTINCT column_name), COUNT (DISTINCT column_name) und SUM (DISTINCT column_name), werden nicht unterstützt, wenn CUBE oder ROLLUP verwendet wird. In diesem Fall gibt SQL Server Database Engine (Datenbankmodul) eine Fehlermeldung zurück und bricht die Abfrage ab.

Hinweise

Ausdrücke in der GROUP BY-Klausel können Spalten der Tabellen, der abgeleiteten Tabellen oder der Sichten in der FROM-Klausel enthalten. Die Spalten müssen nicht in der <select>-Liste der SELECT-Klausel enthalten sein.

Jede Tabellen- oder Sichtspalte in einem Nichtaggregatausdruck in der <select>-Liste muss in die GROUP BY-Liste aufgenommen werden.

• Die folgenden Anweisungen sind zulässig:

  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;
  

• Die folgenden Anweisungen sind nicht zulässig:

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

GROUP BY berechnet einen Summenwert für jede Gruppe, wenn Aggregatfunktionen in der SELECT-Klausel <select list> enthalten sind. Diese Ausdrücke werden als Vektoraggregate bezeichnet.

Zeilen, die die Bedingungen der WHERE-Klausel nicht erfüllen, werden entfernt, bevor irgendwelche Gruppierungsvorgänge ausgeführt werden.

Die HAVING-Klausel wird zusammen mit der GROUP BY-Klausel verwendet, um Gruppen im Resultset zu filtern.

Die GROUP BY-Klausel sortiert das Resultset nicht. Verwenden Sie die ORDER BY-Klausel, um das Resultset zu sortieren.

Wenn eine Gruppierungsspalte NULL-Werte enthält, werden alle NULL-Werte als gleich betrachtet und in einer eigenen Gruppe zusammengefasst.

Sie können GROUP BY nicht zusammen mit einem Alias verwenden, um den Namen einer Tabellen- oder Sichtspalte in der AS-Klausel zu ersetzen, es sei denn, der Alias ersetzt den Spaltennamen einer abgeleiteten Tabelle in der FROM-Klausel.

Doppelte Gruppierungssätze in einer GROUPING SETS-Liste werden nicht entfernt. Doppelte Gruppierungssätze können generiert werden, wenn ein Spaltenausdruck mehr als einmal angegeben wird, oder durch Angabe eines Spaltenausdrucks, der auch von einer CUBE- oder ROLLUP-Anweisung in der GROUPING SETS-Liste erstellt wird.

DISTINCT-Aggregate, wie z. B. AVG (DISTINCT column_name), COUNT (DISTINCT column_name) und SUM (DISTINCT column_name), werden zusammen mit ROLLUP, CUBE und GROUPING SETS unterstützt.

ROLLUP, CUBE und GROUPING SETS können nicht in einer indizierten Sicht angegeben werden.

GROUP BY oder HAVING können auf Spalten des Typs ntext, text oder image nicht direkt angewendet werden. Diese Spalten können als Argumente für Funktionen verwendet werden, die einen Wert mit einem anderen Datentyp zurückgeben, beispielsweise SUBSTRING() und CAST().

Methoden für xml-Datentypen können nicht direkt in einem <Spaltenausdruck> angegeben werden. Verweisen Sie stattdessen auf eine benutzerdefinierte Funktion, die darin enthaltene xml-Datentypmethoden verwendet, oder verweisen Sie auf eine berechnete Spalte, die sie verwendet.

GROUP BY-Einschränkungen für GROUPING SETS, ROLLUP und CUBE

Syntaxeinschränkungen

GROUPING SETS sind in einer GROUP BY-Klausel nicht zugelassen, es sei denn, sie sind Teil einer GROUPING SETS-Liste. Beispielsweise ist GROUP BY C1, (C2,..., Cn) nicht zugelassen, jedoch ist GROUP BY GROUPING SETS (C1, (C2, ..., Cn)) zulässig.

GROUPING SETS sind in GROUPING SETS nicht zulässig. Beispielsweise ist GROUP BY GROUPING SETS (C1, GROUPING SETS (C2, C3)) nicht zulässig.

Die Nicht-ISO-Schlüsselwörter ALL, WITH CUBE und WITH ROLLUP sind in einer GROUP BY-Klausel mit den Schlüsselwörtern ROLLUP, CUBE oder GROUPING SETS nicht zulässig.

Größenbeschränkungen

Für eine einfache GROUP BY-Klausel gibt es keine Beschränkung der Anzahl von Ausdrücken.

Für eine GROUP BY-Klausel, die ROLLUP, CUBE oder GROUPING SETS verwendet, gilt die Maximalzahl von 32 ausdrücken und es können maximal 4096 (212) Gruppierungssätze generiert werden. Die folgenden Beispiele schlagen fehl, weil die GROUP BY-Klausel zu komplex ist:

• In den folgenden Beispielen werden 8192 (213) Gruppierungssätze generiert.

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

• Im folgenden Beispiel werden 4097 (212 + 1) Gruppierungssätze generiert.

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

• Im folgenden Beispiel werden ebenfalls 4097 (212 + 1) Gruppierungssätze generiert. Sowohl die CUBE ()- als auch die ()-Gruppierungssätze generieren eine Gesamtergebniszeile, und doppelte Gruppierungssätze werden nicht entfernt.

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

Unterstützung für ISO und ANSI SQL 2006 GROUP BY-Funktionen

In SQL Server 2012 kann die GROUP BY-Klausel keine Unterabfrage in einem Ausdruck enthalten, der von Liste für die Gruppe verwendet wird. Der Fehler 144 wird zurückgegeben.

SQL Server 2012 unterstützt alle GROUP BY-Funktionen, die im SQL 2006-Standard enthalten sind, außer den folgenden Syntaxausnahmen:

• Gruppierungssätze sind in einer GROUP BY-Klausel nicht zugelassen, es sei denn, sie sind Teil einer expliziten GROUPING SETS-Liste. Beispielsweise ist GROUP BY Column1, (Column2, ...ColumnN) im Standard zulässig, jedoch nicht in SQL Server. GROUP BY C1, GROUPING SETS ((Column2, ...ColumnN)) oder GROUP BY Column1, Column2, ... ColumnN sind zulässig. Diese Klauseln sind mit dem vorherigen GROUP BY-Beispiel semantisch gleichwertig. Damit soll die Möglichkeit vermieden werden, dass GROUP BY Column1, (Column2, ...ColumnN) als GROUP BY C1, GROUPING SETS ((Column2, ...ColumnN)) fehlinterpretiert wird. Diese Klauseln sind semantisch nicht gleichwertig.

• Gruppierungssätze sind in Gruppierungssätzen nicht zulässig. Beispielsweise ist GROUP BY GROUPING SETS (A1, A2,…An, GROUPING SETS (C1, C2, ...Cn)) im SQL-2006-Standard zulässig, jedoch nicht in SQL Server. SQL Server 2012 lässt GROUP BY GROUPING SETS( A1, A2,...An, C1, C2, ...Cn ) oder GROUP BY GROUPING SETS( (A1), (A2), ... (An), (C1), (C2), ... (Cn) ) zu. Diese Beispiele sind zum ersten GROUP BY-Beispiel semantisch gleichwertig und haben eine klarere Syntax.

• GROUP BY [ALL/DISTINCT] sind in einer allgemeinen GROUP BY-Klausel oder mit den Konstrukten GROUPING SETS, ROLLUP, CUBE WITH CUBE oder WITH ROLLUP nicht zulässig. ALL ist der Standard und ist implizit.

Vergleich der unterstützten GROUP BY- Funktionen

In der folgenden Tabelle werden die GROUP BY-Funktionen beschrieben, die abhängig von der Version von SQL Server und dem Datenbank-Kompatibilitätsgrad unterstützt werden.

Funktion	SQL Server 2005 Integration Services	SQL Server, Kompatibilitätsgrad 100 oder höher	SQL Server 2008 oder höher, mit Kompatibilitätsgrad 90
DISTINCT-Aggregate	Nicht unterstützt für WITH CUBE oder WITH ROLLUP.	Unterstützt für WITH CUBE, WITH ROLLUP, GROUPING SETS, CUBE oder ROLLUP.	Wie bei Kompatibilitätsgrad 100
Benutzerdefinierte Funktion mit CUBE- oder ROLLUP-Namen in der GROUP BY-Klausel	Benutzerdefinierte Funktionen dbo.cube (arg1)...argN oder dbo.rollup (arg1,...argN) in der GROUP BY-Klausel sind zulässig. Beispiel: SELECT SUM (x) FROM T GROUP BY dbo.cube(y);	Benutzerdefinierte Funktionen dbo.cube (arg1,...argN) oder dbo.rollup (arg1,...argN) in der GROUP BY-Klausel sind nicht zulässig. Beispiel: SELECT SUM (x) FROM T GROUP BY dbo.cube(y); Es wird folgende Fehlermeldung zurückgegeben: „Falsche Syntax in der Nähe des 'cube'|'rollup'-Schlüsselworts”. Ersetzen Sie dbo.cube durch [dbo].[cube] oder dbo.rollup durch [dbo].[rollup], um dieses Problem zu vermeiden. Das folgende Beispiel ist zulässig: SELECT SUM (x) FROM T GROUP BY [dbo].[cube](y);	Benutzerdefinierte Funktionen dbo.cube (arg1,...argN) oder dbo.rollup (arg1,...argN) in der GROUP BY-Klausel sind zulässig. Beispiel: SELECT SUM (x) FROM T GROUP BY dbo.cube(y);
GROUPING SETS	Nicht unterstützt	Unterstützt	Unterstützt
CUBE	Nicht unterstützt	Unterstützt	Nicht unterstützt
ROLLUP	Nicht unterstützt	Unterstützt	Nicht unterstützt
Gesamtergebnis, z. B. GROUP BY ()	Nicht unterstützt	Unterstützt	Unterstützt
GROUPING_ID-Funktion	Nicht unterstützt	Unterstützt	Unterstützt
GROUPING-Funktion	Unterstützt	Unterstützt	Unterstützt
WITH CUBE	Unterstützt	Unterstützt	Unterstützt
WITH ROLLUP	Unterstützt	Unterstützt	Unterstützt
WITH CUBE- oder WITH ROLLUP-Entfernung "doppelter" Gruppierungen	Unterstützt	Unterstützt	Unterstützt

Beispiele

A.Verwenden einer einfachen GROUP BY-Klausel

Im folgenden Beispiel wird die Summe für jede SalesOrderID aus der SalesOrderDetail-Tabelle abgerufen.

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

B.Verwenden einer GROUP BY-Klausel mit mehreren Tabellen

Im folgenden Beispiel wird die Anzahl von Mitarbeitern für die einzelnen City-Spalten der Address-Tabelle abgerufen, die mit der EmployeeAddress-Tabelle verknüpft ist.

USE AdventureWorks2012;
GO
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.Verwenden einer GROUP BY-Klausel mit einem Ausdruck

Im folgenden Beispiel wird der Gesamtumsatz für jedes Jahr mithilfe der DATEPART-Funktion abgerufen. Derselbe Ausdruck muss sowohl in der SELECT-Liste als auch in der GROUP BY-Klausel vorhanden sein.

USE AdventureWorks2012;
GO
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.Verwenden einer GROUP BY-Klausel mit einer HAVING-Klausel

Im folgenden Beispiel wird die HAVING-Klausel verwendet, um anzugeben, welche der in der GROUP BY-Klausel generierten Gruppen in das Resultset aufgenommen werden soll.

USE AdventureWorks2012;
GO
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);

Siehe auch

Verweis

GROUPING_ID (Transact-SQL)

GROUPING (Transact-SQL)

SELECT (Transact-SQL)

SELECT-Klausel (Transact-SQL)