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 table virtuelle est créée après l’évaluation de la clause FROM et de la clause WHERE. Le contenu de la table virtuelle est désormais disponible pour un traitement supplémentaire. 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 décrire le processus de GROUP BY est que toutes les lignes ayant la même valeur pour CustomerID sont regroupées et retournées dans une ligne de résultat unique.
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 GROUP BY et DISTINCT ? Si vous souhaitez simplement savoir quelles sont les valeurs distinctes pour 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és 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 dans 89 groupes, en fonction des valeurs de 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, le résultat nous montre combien de lignes de la table SalesOrderHeader appartiennent à chaque client.
CustomerID
OrderCount
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 fier à ce comportement. Si vous avez besoin de trier les résultats, 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
OrderCount
1005
1
1234
3
Les clauses dans une instruction SELECT sont appliquées dans l’ordre suivant :
- FROM
- WHERE
- GROUP BY
- HAVING
- SELECT
- ORDER BY
Les alias de colonne sont affectés dans la clause SELECT, ce 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 génère 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 aboutit, en regroupant et en triant les résultats par ID client.
SELECT CustomerID AS Customer,
COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY CustomerID
ORDER BY Customer;
Résolution des erreurs GROUP BY
Une difficulté courante avec l’utilisation de GROUP BY dans les instructions SELECT consiste est de comprendre pourquoi le type de message d’erreur suivant se produit :
Msg 8120, Niveau 16, État 1, Ligne 2 La colonne <nom_colonne> 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 de la clause GROUP BY ou une fonction d’agrégation opérant sur chaque groupe :
SELECT CustomerID, COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY CustomerID;
La requête suivante renvoie une erreur, car PurchaseOrderNumber ne fait pas partie du GROUP BY et n’est pas utilisé 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 voir les choses. Cette requête retourne une ligne pour chaque valeur CustomerID. Mais les lignes pour le même CustomerID peuvent avoir différentes valeurs PurchaseOrderNumber, alors quelles valeurs doivent être retournées ?
Si vous souhaitez afficher les commandes par ID client et 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 renverra une ligne pour chaque combinaison de client et de bon de commande, ainsi que le nombre de commandes pour cette combinaison.