Résumer les données avec GROUP BY
Bien que les fonctions d’agrégation soient utiles pour l’analyse, vous pouvez organiser vos données en sous-ensembles avant de les résumer. Dans cette section, vous allez apprendre à effectuer cette opération à l’aide de la clause GROUP BY.
Utilisation de la clause GROUP BY
Comme vous l’avez appris, lorsque votre instruction SELECT est traitée, une fois la clause FROM et la clause WHERE évaluées, une table virtuelle est créée. Le contenu de la table virtuelle est désormais disponible pour un traitement ultérieur. Vous pouvez utiliser la clause GROUP BY pour subdiviser le contenu de cette table virtuelle en groupes de lignes.
Pour regrouper des lignes, spécifiez un ou plusieurs éléments dans la clause GROUP BY :
GROUP BY <value1> [, <value2>, …]
GROUP BY crée des groupes et place des lignes dans chaque groupe, comme déterminé par les éléments spécifiés dans la clause.
Par exemple, la requête suivante génère un ensemble de lignes groupées, une ligne par CustomerID dans la table Sales.SalesOrderHeader . Une autre façon de regarder le processus GROUP BY est que toutes les lignes avec la même valeur pour CustomerID seront regroupées et retournées dans une seule ligne de résultat.
SELECT CustomerID
FROM Sales.SalesOrderHeader
GROUP BY CustomerID;
La requête ci-dessus équivaut à la requête suivante :
SELECT DISTINCT CustomerID
FROM Sales.SalesOrderHeader
Une fois que la clause GROUP BY a été traitée et que chaque ligne a été associée à un groupe, les phases ultérieures de la requête doivent agréger tous les éléments des lignes sources qui se trouvent dans la liste SELECT, mais qui n’apparaissent pas dans la liste GROUP BY. Cette exigence aura un impact sur la façon dont vous écrivez vos clauses SELECT et HAVING.
Alors, quelle est la différence entre l’écriture de la requête avec un GROUP BY ou un DISTINCT ? Si tout ce que vous souhaitez savoir est les valeurs distinctes de CustomerID, il n’y a aucune différence. Toutefois, avec GROUP BY, nous pouvons ajouter d’autres éléments à la liste SELECT qui sont ensuite agrégées pour chaque groupe.
La fonction d’agrégation la plus simple est COUNT(*). La requête suivante prend les 830 lignes sources d’origine de CustomerID et les regroupe en 89 groupes, en fonction des valeurs CustomerID . Chaque valeur CustomerID distincte génère une ligne de sortie dans la requête GROUP BY
SELECT CustomerID, COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY CustomerID;
Pour chaque valeur CustomerID , la requête agrège et compte les lignes. Par conséquent, nous avons obtenu le nombre de lignes de la table SalesOrderHeader appartenant à chaque client.
CustomerID
NombreDeCommandes
1234
3
1005
1
Notez que GROUP BY ne garantit pas l’ordre des résultats. Souvent, en raison de la façon dont l’opération de regroupement est effectuée par le processeur de requêtes, les résultats sont retournés dans l’ordre des valeurs de groupe. Toutefois, vous ne devez pas vous appuyer sur ce comportement. Si vous avez besoin que les résultats soient triés, vous devez inclure explicitement une clause ORDER :
SELECT CustomerID, COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY CustomerID
ORDER BY CustomerID;
Cette fois, les résultats sont retournés dans l’ordre spécifié :
CustomerID
NombreDeCommandes
1005
1
1234
3
Les clauses d’une instruction SELECT sont appliquées dans l’ordre suivant :
- FROM
- WHERE
- REGROUPER PAR
- AYANT
- SELECT
- ORDONNER PAR
Les alias de colonne sont attribués dans la clause SELECT, qui se produit après la clause GROUP BY, mais avant la clause ORDER BY. Vous pouvez référencer un alias de colonne dans la clause ORDER BY, mais pas dans la clause GROUP BY. La requête suivante entraîne une erreur de nom de colonne non valide :
SELECT CustomerID AS Customer,
COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY Customer
ORDER BY Customer;
Toutefois, la requête suivante réussit, regroupe et trie les résultats par l’ID client.
SELECT CustomerID AS Customer,
COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY CustomerID
ORDER BY Customer;
Résolution des erreurs GROUP BY
Un obstacle courant à l’utilisation de GROUP BY dans les instructions SELECT est de comprendre pourquoi le type de message d’erreur suivant se produit :
Msg 8120, Level 16, State 1, Line 2 Column <column_name> n’est pas valide dans la liste de sélection, car elle n’est pas contenue dans une fonction d’agrégation ou dans la clause GROUP BY.
Par exemple, la requête suivante est autorisée, car chaque colonne de la liste SELECT est une colonne dans la clause GROUP BY ou une fonction d’agrégation fonctionnant sur chaque groupe :
SELECT CustomerID, COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY CustomerID;
La requête suivante retourne une erreur, car PurchaseOrderNumber ne fait pas partie du GROUPE BY et n’est pas utilisée avec une fonction d’agrégation.
SELECT CustomerID, PurchaseOrderNumber, COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY CustomerID;
Cette requête retourne l’erreur :
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.
Voici une autre façon de le penser. Cette requête retourne une ligne pour chaque valeur CustomerID . Toutefois, les lignes du même CustomerID peuvent avoir des valeurs PurchaseOrderNumber différentes. Par conséquent, quelles sont les valeurs qui doivent être retournées ?
Si vous souhaitez voir les commandes par ID client et par bon de commande, vous pouvez ajouter la colonne PurchaseOrderNumber à la clause GROUP BY, comme suit :
SELECT CustomerID, PurchaseOrderNumber, COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY CustomerID, PurchaseOrderNumber;
Cette requête retourne une ligne pour chaque client et chaque combinaison de bons de commande, ainsi que le nombre de commandes pour cette combinaison.