GROUP BY (Transact-SQL)

Groupe un ensemble sélectionné de lignes dans un ensemble de lignes de synthèse par les valeurs d'une ou plusieurs colonnes ou expressions dans SQL Server 2008 R2. Une ligne est retournée pour chaque de groupe. Les fonctions d'agrégation dans la clause SELECT <select list> fournissent des informations à propos de chaque de groupe plutôt que des lignes individuelles.

La clause GROUP BY a une syntaxe conforme à ISO et une syntaxe non-conforme à ISO. Un seul style de syntaxe peut être utilisé dans une même instruction SELECT. Utilisez la syntaxe conforme à ISO pour tout nouveau travail. La syntaxe non-conforme à ISO est fournie à des fins de compatibilité descendante.

Dans cette rubrique, une clause GROUP BY peut être décrite comme générale ou simple :

• Une clause GROUP BY générale inclut des GROUPING SETS, CUBE, ROLLUP, WITH CUBE ou WITH ROLLUP.

• Une clause GROUP BY simple n'inclut pas de GROUPING SETS, CUBE, ROLLUP, WITH CUBE ou WITH ROLLUP. GROUP BY (), grand total, est considéré un GROUP BY simple.

Conventions de syntaxe de Transact-SQL (Transact-SQL)

Syntaxe

        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 } ] 
]

Arguments

• <column_expression>
  Expression sur laquelle l'opération de regroupement est effectuée.

• ROLLUP ( )
  Génère les lignes d'agrégation GROUP BY simples, plus des lignes de sous-total ou de super-agrégat, ainsi qu'une ligne de total global.

  Le nombre de regroupements retourné est égal au nombre d'expressions dans la <liste d'éléments composites> plus un. Par exemple, considérez l'instruction suivante.

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

  Une ligne avec un sous-total est générée pour chaque combinaison unique de valeurs de (a, b, c), (a, b) et (a). Une ligne de total global est également calculée.

  Les colonnes sont classées de droite à gauche. L'ordre des colonnes affecte les regroupements de sortie de ROLLUP et peut affecter le nombre de lignes dans le jeu de résultats.

• CUBE ( )
  Génère des lignes d'agrégation GROUP BY simples, les lignes de super-agrégat ROLLUP et des lignes de tabulation croisée.

  CUBE génère un regroupement pour toutes les permutations d'expressions dans la <liste d'éléments composites>.

  Le nombre de regroupements générés est égal à (2n), où n = le nombre d'expressions dans la <liste d'éléments composites>. Par exemple, considérez l'instruction suivante.

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

  Une ligne est produite pour chaque combinaison unique de valeurs de (a, b, c), (a, b), (a, c), (b, c), (a), (b) et (c), avec un sous-total pour chaque ligne et un total de lignes global.

  L'ordre des colonnes n'affecte pas la sortie de CUBE.

• GROUPING SETS ( )
  Spécifie plusieurs regroupements de données dans une requête. Seuls les groupes spécifiés sont regroupés, au lieu du jeu complet d'agrégations générées par CUBE ou ROLLUP. Les résultats sont l'équivalent de l'opération UNION ALL des groupes spécifiés. GROUPING SETS peuvent contenir un élément unique ou une liste d'éléments. GROUPING SETS peut spécifier des regroupements équivalents à ceux retournés par ROLLUP ou CUBE. Pour obtenir des exemples, consultez Équivalents de GROUPING SETS. La <liste d'éléments de jeu de regroupement> peut contenir ROLLUP ou CUBE.

• ( )
  Le groupe vide génère un total.

Syntaxe non conforme à ISO

• ALL
  Cette fonctionnalité sera supprimée dans une prochaine version de Microsoft SQL Server. Évitez d'utiliser cette fonctionnalité dans de nouveaux travaux de développement et prévoyez de modifier les applications qui utilisent actuellement cette fonctionnalité. Comprend tous les groupes et jeux de résultats, même ceux qui ne contiennent aucune ligne répondant aux conditions de recherche spécifiées dans la clause WHERE. Lorsque ALL est spécifié, les valeurs NULL sont retournées aux colonnes de résumé de groupes ne répondant pas aux conditions de recherche. Vous ne pouvez pas spécifier ALL avec les opérateurs CUBE ou ROLLUP.

  L'opération GROUP BY ALL n'est pas prise en charge dans les requêtes qui demandent l'accès aux tables distantes si une clause WHERE est également présente dans la requête. GROUP BY ALL échoue sur les colonnes qui ont l'attribut FILESTREAM.

• group_by_expression
  Expression sur laquelle est effectué un regroupement. L'argument group_by_expression porte également le nom de colonne de regroupement. L'argument group_by expression peut être une colonne ou une expression de non-agrégation qui désigne une colonne retournée par la clause FROM. Vous ne pouvez pas utiliser un alias de colonne défini dans la liste SELECT pour spécifier une colonne de regroupement.

  Notes

  Vous ne pouvez pas utiliser les colonnes de type text, ntext et image dans group_by_expression.

  Pour les clauses GROUP BY ne contenant pas CUBE ou ROLLUP, le nombre d'éléments de group_by_expression est limité aux tailles de colonne GROUP BY, aux colonnes agrégées et aux valeurs d'agrégation impliquées dans la requête. Cette limite provient de la limite de 8 060 octets de la table de travail intermédiaire requise pour stocker les résultats de requêtes intermédiaires. Un maximum de 12 expressions de regroupement est autorisé quand CUBE ou ROLLUP est spécifié.

  Les méthodes de types de données xml ne peuvent pas être directement spécifiées dans group_by_expression. Consultez plutôt une fonction définie par l'utilisateur qui a recours aux méthodes des types de données xml, ou consultez une colonne calculée qui les utilise.

• WITH CUBE
  Cette fonctionnalité sera supprimée dans une prochaine version de Microsoft SQL Server. Évitez d'utiliser cette fonctionnalité dans de nouveaux travaux de développement et prévoyez de modifier les applications qui utilisent actuellement cette fonctionnalité. Spécifie qu'en plus des lignes habituelles fournies par GROUP BY, des lignes de synthèse sont introduites dans le jeu de résultats. Une ligne de synthèse GROUP BY est retournée pour toute combinaison possible de groupes et de sous-groupes dans le jeu de résultats. Utilisez la fonction GROUPING pour déterminer si les valeurs NULL du jeu de résultats sont des valeurs de résumé GROUP BY.

  Le nombre de lignes de résumé dans le jeu de résultats est déterminé par le nombre de colonnes incluses dans la clause GROUP BY. Dans la mesure où CUBE retourne toutes les combinaisons de groupes et de sous-groupes possibles, le nombre de lignes est le même, quel que soit l'ordre dans lequel les colonnes de regroupement sont spécifiées.

• WITH ROLLUP
  Cette fonctionnalité sera supprimée dans une prochaine version de Microsoft SQL Server. Évitez d'utiliser cette fonctionnalité dans de nouveaux travaux de développement et prévoyez de modifier les applications qui utilisent actuellement cette fonctionnalité. Spécifie qu'en plus des lignes habituelles fournies par GROUP BY, des lignes de synthèse sont introduites dans le jeu de résultats. Les groupes sont résumés dans un ordre hiérarchique, du plus bas au plus haut niveau de groupe. La hiérarchie du groupe est déterminée par l'ordre dans lequel les colonnes de regroupement sont spécifiées. La modification de l'ordre des colonnes de regroupement peut influer sur le nombre de lignes produites dans le jeu de résultats.

  Important

  Les fonctions d'agrégation Distinct, par exemple AVG(DISTINCT column_name), COUNT(DISTINCT column_name) et SUM(DISTINCT column_name), ne sont pas prises en charge lorsque vous utilisez CUBE ou ROLLUP. En cas d'utilisation, le Moteur de base de données SQL Server retourne un message d'erreur et annule la requête.

Notes

Les expressions dans la clause GROUP BY peuvent contenir des colonnes des tables, tables dérivées ou vues dans la clause FROM. Il n'est pas obligatoire que les colonnes apparaissent dans la clause SELECT <select list>.

Chaque table ou colonne de vue dans une expression la <select liste> doit être incluse dans la liste GROUP BY :

• Les instructions suivantes sont autorisées :

  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;
  

• Les instructions suivantes ne sont pas autorisées :

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

Si les fonctions d'agrégation sont comprises dans la clause SELECT <select list>, GROUP BY calcule une valeur de résumé pour chaque groupe. Ces expressions sont dites agrégations vectorielles.

Les lignes qui ne répondent pas aux conditions dans la clause WHERE sont supprimées avant toute opération de regroupement.

La clause HAVING est utilisée avec la clause GROUP BY pour filtrer des groupes dans le jeu de résultats.

La clause GROUP BY ne classe pas le jeu de résultats. Utilisez la clause ORDER BY pour classer le jeu de résultats.

Si une colonne de regroupement contient des valeurs Null, toutes les valeurs Nullsont considérées comme égales et sont placées dans un même groupe.

Vous ne pouvez pas utiliser GROUP BY avec un alias pour remplacer un nom de colonne dans la clause AS, à moins que l'alias ne remplace un nom de colonne dans une table dérivée dans la clause FROM.

Les jeux de regroupement en double dans une liste GROUPING SETS ne sont pas éliminés. Les jeux de regroupement en double peuvent être générés en spécifiant une expression de colonne plusieurs fois ou en répertoriant une expression de colonne générée également par un CUBE ou ROLLUP dans les listes GROUPING SETS.

Les fonctions d'agrégation Distinct, par exemple AVG (DISTINCT column_name), COUNT (DISTINCT column_name), and SUM (DISTINCT column_name) sont prises en charge avec ROLLUP, CUBE et GROUPING SETS.

ROLLUP, CUBE et GROUPING SETS ne peuvent pas être spécifiés dans une vue indexée.

GROUPEZ PAR ou HAVING ne peuvent pas être utilisés directement sur des colonnes de ntext, text ou image. Ces colonnes peuvent être utilisées comme arguments dans des fonctions qui renvoient une valeur d'un autre type de données, telles que SUBSTRING() et CAST().

Les méthodes de types de données xml ne peuvent pas être directement spécifiées dans une <expression de colonne>. Consultez plutôt une fonction définie par l'utilisateur qui a recours aux méthodes des types de données xml, ou consultez une colonne calculée qui les utilise.

Limitations de GROUP BY pour GROUPING SETS, ROLLUP et CUBE

Limitations de syntaxe

Les GROUPING SETS ne sont pas autorisés dans la clause GROUP BY, à moins de faire partie d'une liste GROUPING SETS. Par exemple, GROUP BY C1, (C2,..., Cn) n'est pas autorisé, contrairement à GROUP BY GROUPING SETS (C1, (C2, ..., Cn)).

Les GROUPING SETS ne sont pas autorisés à l'intérieur des GROUPING SETS. Par exemple, GROUP BY GROUPING SETS (C1, GROUPING SETS (C2, C3)) n'est pas autorisé.

Les mots clés non-ISO ALL, WITH CUBE et WITH ROLLUP ne sont pas autorisés dans une clause GROUP BY avec les mots clés ROLLUP, CUBE ou GROUPING SETS.

Limitations de taille

Pour une clause GROUP BY simple, il n'y a aucune limite quant au nombre d'expressions.

Pour une clause GROUP BY qui utilise ROLLUP, CUBE ou GROUPING SETS, le nombre maximal d'expressions est 32 et le nombre maximal de jeux de regroupement qui peuvent être générés est 4096 (212). Les exemples suivants échouent car la clause GROUP BY est trop complexe :

• Les exemples suivants génèrent 8192 (213) jeux de regroupement.

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

• L'exemple suivant génère 4097 (212 + 1) jeux de regroupement.

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

• L'exemple suivant génère également 4097 (212 + 1) jeux de regroupement. CUBE () et le jeu de regroupement () produisent une ligne de total global et les jeux de regroupement en double ne sont pas éliminés.

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

Prise en charge des fonctionnalités GROUP BY ISO et ANSI SQL-2006

Dans SQL Server 2008 et les versions ultérieures, la clause GROUP BY ne peut pas contenir de sous-requête dans une expression utilisée pour la liste GROUP BY. L'erreur 144 est retournée.

SQL Server 2008 et les versions ultérieures prennent en charge toutes les fonctionnalités GROUP BY incluses dans la norme SQL-2006, avec les exceptions de syntaxe suivantes :

• Les jeux de regroupement ne sont pas autorisés dans la clause GROUP BY, à moins de faire partie d'une liste GROUPING SETS explicite. Par exemple, GROUP BY Column1, (Column2, ...ColumnN) est autorisé dans la norme, mais pas dans SQL Server. GROUP BY C1, GROUPING SETS ((Column2, ...ColumnN)) ou GROUP BY Column1, Column2, ... ColumnN est autorisé. Ils sont sémantiquement équivalents à l'exemple GROUP BY précédent. Cela est destiné à éviter que GROUP BY Column1, (Column2, ...ColumnN) soit interprété de façon incorrecte comme GROUP BY C1, GROUPING SETS ((Column2, ...ColumnN)). Cela n'est pas équivalent sémantiquement.

• Les jeux de regroupement ne sont pas autorisés à l'intérieur des jeux de regroupement. Par exemple, GROUP BY GROUPING SETS (A1, A2,…An, GROUPING SETS (C1, C2, ...Cn)) est autorisé dans la norme SQL-2006, mais pas dans SQL Server. SQL Server 2008 et les versions ultérieures autorisent GROUP BY GROUPING SETS( A1, A2,...An, C1, C2, ...Cn ) ou GROUP BY GROUPING SETS( (A1), (A2), ... (An), (C1), (C2), ... (Cn) ). Ces exemples sont sémantiquement équivalents au premier exemple GROUP BY et ont une syntaxe plus claire.

• GROUP BY [ALL/DISTINCT] n'est pas autorisé dans une clause GROUP BY générale, ni avec les constructions GROUPING SETS, ROLLUP, CUBE, WITH CUBE ou WITH ROLLUP. ALL est la valeur par défaut et est implicite.

Comparaison des fonctionnalités GROUP BY prises en charge

Le tableau suivant décrit les fonctionnalités GROUP BY prises en charge en fonction de la version de SQL Server et du niveau de compatibilité de la base de données.

Fonctionnalité	SQL Server 2005 Integration Services	SQL Server 2008 - Niveau de compatibilité 100	SQL Server 2008 - Niveau de compatibilité 90 ou moins
Agrégats DISTINCT	Non pris en charge pour WITH CUBE ou WITH ROLLUP.	Pris en charge pour WITH CUBE, WITH ROLLUP, GROUPING SETS, CUBE ou ROLLUP.	Identique au niveau de comptabilité 100 de SQL Server 2008.
Fonction définie par l'utilisateur avec nom CUBE ou ROLLUP dans la clause GROUP BY	Les fonctions définies par l'utilisateur dbo.cube(arg1)...argN ou dbo.rollup(arg1,...argN) dans la clause GROUP BY sont autorisées. Par exemple : SELECT SUM (x) FROM T GROUP BY dbo.cube(y);	Les fonctions définies par l'utilisateur dbo.cube (arg1,...argN) ou dbo.rollup(arg1,...argN) dans la clause GROUP BY ne sont pas autorisées. Par exemple : SELECT SUM (x) FROM T GROUP BY dbo.cube(y); Le message d'erreur suivant est retourné : « Syntaxe incorrecte vers le mot clé 'cube'|'rollup' ». Pour éviter ce problème, remplacez dbo.cube par [dbo].[cube] ou dbo.rollup par [dbo].[rollup]. L'exemple suivant est autorisé : SELECT SUM (x) FROM T GROUP BY [dbo].[cube](y);	Les fonctions définies par l'utilisateur dbo.cube (arg1,...argN) ou dbo.rollup(arg1,...argN) dans la clause GROUP BY sont autorisées. Par exemple : SELECT SUM (x) FROM T GROUP BY dbo.cube(y);
GROUPING SETS	Non pris en charge	Pris en charge	Pris en charge
CUBE	Non pris en charge	Pris en charge	Non pris en charge
ROLLUP	Non pris en charge	Pris en charge	Non pris en charge
Total global, tel que GROUP BY ()	Non pris en charge	Pris en charge	Pris en charge
GROUPING_ID, fonction	Non pris en charge	Pris en charge	Pris en charge
GROUPING, fonction	Pris en charge	Pris en charge	Pris en charge
WITH CUBE	Pris en charge	Pris en charge	Pris en charge
WITH ROLLUP	Pris en charge	Pris en charge	Pris en charge
Suppression de regroupement WITH CUBE ou WITH ROLLUP « en double »	Pris en charge	Pris en charge	Pris en charge

Exemples

Pour obtenir des exemples qui utilisent GROUPING SETS, ROLLUP et CUBE, consultez Utilisation de GROUP BY avec ROLLUP, CUBE et GROUPING SETS.

A. Utilisation d'une clause GROUP BY simple

L'exemple suivant extrait le total pour chaque SalesOrderID de la table SalesOrderDetail.

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

B. Utilisation d'une clause GROUP BY avec plusieurs tables

L'exemple suivant extrait le nombre d'employés pour chaque City de la table Address en conjonction avec la table EmployeeAddress.

USE AdventureWorks2008R2;
GO
SELECT a.City, COUNT(bea.AddressID) AS 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. Utilisation d'une clause GROUP BY avec une expression

L'exemple suivant récupère le total des ventes pour chaque année en utilisant la fonction DATEPART. La même expression doit être présente dans la liste SELECT et dans la clause GROUP BY.

USE AdventureWorks2008R2;
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. Utilisation d'une clause GROUP BY avec une clause HAVING

L'exemple suivant utilise la clause HAVING pour spécifier lequel des groupes générés dans la clause GROUP BY doit être inclus dans le jeu de résultats.

USE AdventureWorks2008R2;
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);

Voir aussi

Référence

GROUPING_ID (Transact-SQL)

GROUPING (Transact-SQL)

SELECT (Transact-SQL)

Clause SELECT (Transact-SQL)

Concepts

Utilisation de GROUP BY avec ROLLUP, CUBE et GROUPING SETS

Équivalents de GROUPING SETS

Résolution des erreurs GROUP BY