Comprendre la clause GROUP BY

GROUP BY est une partie importante de l'instruction SQL SELECT. Mais les nouveaux codeurs SQL peuvent rencontrer des problèmes lorsque cette clause est utilisée de manière incorrecte. Voici comment éviter ces problèmes.

Vous apprenez le SQL. Vous savez comment SÉLECTIONNER des données à partir d'une table et comment filtrer les données à l'aide d'une clause WHERE. Vous pouvez traiter les données à l'aide de fonctions d'agrégation (MIN, MAX, SUM, AVG, etc.). Mais lorsque vous avez affaire à un grand nombre de données, vous pouvez avoir besoin de les réduire encore plus. C'est là qu'intervient la clause GROUP BY, qui vous permet d'organiser les informations en fonction des paramètres que vous avez définis.

Dans cet article, nous allons vous expliquer comment utiliser GROUP BY. Nous aborderons également quelques problèmes possibles et la manière de les éviter ou de les résoudre.

Utilisation de GROUP BY

Pour comprendre comment utiliser GROUP BY, nous devons d'abord définir une table sur laquelle nous allons nous exercer. Que diriez-vous d'un tableau décrivant les personnes ?

| ID | Name     | Gender | Height | Weight | Eye_color |
--------------------------------------------------------
|  1 | Mark     | Male   |    180 |     78 | Blue      |
|  2 | Susan    | Female |    172 |     59 | Brown     |
|  3 | Thomas   | Male   |    179 |     92 | Brown     |
|  4 | Katarina | Female |    164 |     53 | Green     |
|  5 | Mindy    | Female |    170 |     58 | Blue      |
--------------------------------------------------------

Nous avons donc notre tableau d'entraînement. Nous allons maintenant nous intéresser aux statistiques. Par exemple, quelle est la taille moyenne de toutes nos personnes ? Pour le savoir, nous tapons :

SELECT AVG(Height)
FROM People

+-------------+
| AVG(Height) |
+-------------+
|    173.0000 |
+-------------+

Supposons maintenant que nous voulions connaître la taille moyenne par sexe. Cela semble assez simple, il suffit d'ajouter une clause WHERE. Nous tapons donc :

SELECT AVG(Height)
FROM People
WHERE Gender = ‘Male’

Mais que se passe-t-il si nous introduisons d'autres sexes dans notre tableau ? Dans ce cas, nous devrions écrire des requêtes supplémentaires et collecter manuellement les données dont nous avons besoin. Il est plus facile de GROUPER nos données PAR Gender, puis de calculer la taille moyenne pour chaque groupe, comme indiqué ci-dessous.

SELECT Gender, AVG(Height)
FROM People
GROUP BY Gender

+--------+-------------+
| Gender | AVG(Height) |
+--------+-------------+
| Female |    168.6667 |
| Male   |    179.5000 |
+--------+-------------+

Le regroupement semble facile, non ? Tout ce que vous devez faire, c'est ajouter la clause GROUP BY à votre instruction SQL. Supposons toutefois que nous ayons besoin de deux paramètres dans notre recherche. Dans ce cas, nous devons regrouper par deux colonnes. Disons que nous voulons savoir combien d'hommes et de femmes ont les yeux bleus, bruns ou verts. Nous devrions taper :

SELECT Gender, Eye_color, COUNT(*)
FROM People
GROUP BY Gender, Eye_color

+--------+-----------+----------+
| Gender | Eye_color | COUNT(*) |
+--------+-----------+----------+
| Female | Blue      |        1 |
| Female | Brown     |        1 |
| Female | Green     |        1 |
| Male   | Blue      |        1 |
| Male   | Brown     |        1 |
+--------+-----------+----------+

Ceci n'est qu'une introduction à GROUP BY. Vous pouvez l'utiliser de nombreuses façons. Pourquoi ne pas essayer d'expérimenter différentes fonctions d'agrégation (comme AVG et COUNT) pour mieux comprendre GROUP BY ?

Erreurs courantes liées à l'utilisation de GROUP BY

Bien que GROUP BY semble assez facile à utiliser, il est fréquent que les débutants en SQL se retrouvent confrontés à des messages d'erreur déroutants. En voici quelques-uns que nous rencontrons assez souvent :

1. Sélection de plusieurs valeurs

La chose la plus importante à retenir lorsque vous utilisez GROUP BY est que ce que vous allez SÉLECTIONNER doit être une valeur unique. C'est pourquoi nous devons utiliser une fonction d'agrégation : elle prend plusieurs valeurs et en produit une seule à retourner.

Pour rendre les choses un peu plus claires, examinons nos groupes :

+--------+--------+
| Gender | Height |
+--------+--------+
| Male   |    180 |
|        |    179 |
| Female |    172 |
|        |    164 |
|        |    170 |
+--------+--------+

Lorsque nous demandons Hauteur groupée par Gender, nous voulons obtenir une seule valeur. Mais ici, Male a deux valeurs pour la hauteur et Female en a trois. Laquelle devons-nous choisir ?

SELECT Gender, Height
FROM People
GROUP BY Gender;

(MYSQL) ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains 
nonaggregated column 'vertabelo.People.Height' which is not functionally dependent on columns 
in GROUP BY clause;

C'est pourquoi nous devons utiliser une fonction d'agrégation (telle que AVG, que nous avons utilisée précédemment) pour obtenir une seule valeur.

2. Utilisation de WHERE pour filtrer les résultats

Supposons que nous voulions uniquement voir les résultats de la couleur des yeux pour les groupes de personnes mesurant plus de 170 cm. Si nous essayons de mettre cela dans la clause WHERE, comme indiqué ci-dessous :

SELECT Gender, Eye_color, COUNT(*)
FROM People
WHERE AVG(Height) > 170
GROUP BY Gender, Eye_color

Nous obtenons le message d'erreur illustré ci-dessous :

(MYSQL) ERROR 1111 (HY000): Invalid use of group function

C'est parce que la base de données regroupe nos enregistrements après les avoir filtrés. Nous voulons qu'ils filtrent le résultat de l'instruction GROUP BY. Pour ce faire, nous utilisons une autre clause appelée HAVING. Elle vient après GROUP BY et fonctionne comme WHERE. La différence est que vous pouvez utiliser des fonctions d'agrégation dans cette clause. Pour obtenir l'ensemble de résultats souhaité, nous devons écrire ce code :

SELECT Gender, Eye_color, COUNT(*)
FROM People
GROUP BY Gender, Eye_color
HAVING AVG(Height) > 170

Rappelez-vous que dans la base de données, une instruction SQL SELECT s'exécute toujours dans cet ordre :

1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY

Nous prenons une source de données, nous filtrons les enregistrements, nous les regroupons, nous filtrons les groupes, nous sélectionnons les colonnes que nous voulons, puis nous les trions.

3. Erreurs d'entités multiples

Ceci est un peu plus avancé. Lorsque nous avons JOINT plusieurs tables, il est tout à fait possible que certaines entités aient les mêmes noms. Nous voudrons donc souvent que les résultats soient regroupés par ID d'entité plutôt que par nom d'entité.

Par exemple, supposons que vous recherchez des données sur la ville de Varsovie. Il y a une Varsovie qui est la capitale de la Pologne et une Varsovie qui se trouve dans l'État de l'Indiana, aux États-Unis.

Disons que nous avons une table définie comme suit :

Table City:
---------------------
| ID | Name | Award | 
---------------------

Cette table décrit les villes qui ont reçu un ou plusieurs prix. Une ville est identifiée par son ID et elle peut avoir plusieurs récompenses.

Si nous voulions voir le nombre de récompenses reçues par une ville et avoir cette information groupée par nom de ville, nous utiliserions peut-être cette requête :

SELECT City.Name, COUNT(Award)
FROM City
GROUP BY City.Name

Le problème est qu'en utilisant City.Name, les récompenses de Varsovie, en Pologne, et de Varsovie, en Indiana, seraient additionnées. Après tout, les deux villes sont des Varsovie ! Cependant, il s'agit de lieux différents, et en tant que tels, ils ont des valeurs différentes sur City.ID. Si vous considérez une ville comme une entité de base de données, elle est identifiée par son ID plutôt que par ses attributs (comme Name). Si nous regroupons les résultats par ID, nous obtiendrons les informations correctes. Puisque nous voulons toujours afficher le nom, nous utiliserons quelque chose comme ceci :

SELECT City.Name, COUNT(Award)
FROM City
GROUP BY City.ID

Le résultat comportera des entrées séparées pour les différentes Warsaw avec les valeurs que nous voulons. (Normalement, il y aurait une autre colonne, comme "pays" ou "état", pour différencier ces deux villes. Mais pour les besoins de l'exemple, disons qu'il n'y en a pas).

4. Utilisation de valeurs qui ne sont pas "agrégées".

Dans l'exemple ci-dessus, nous avons sélectionné l'attribut City.Name et regroupé les résultats par l'attribut City.ID. Dans notre table, chaque enregistrement ayant le même ID a également le même nom de ville. Certaines bases de données n'auront aucun problème avec cela et renverront les résultats attendus, mais d'autres vous donneront une erreur disant que City.Name n'est pas dans la clause GROUP BY et qu'il n'est pas agrégé. J'ai reproduit le message d'erreur MySQL ci-dessous :

(MYSQL) ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains 
nonaggregated column 'vertabelo.City.Name' which is not functionally dependent on columns 
in GROUP BY clause;

Mais il s'agit d'une seule valeur ! Comment corriger cette erreur ? Eh bien, puisqu'un City.ID signifie exactement un City.Name, nous pouvons les mettre tous les deux dans la clause GROUP BY:

SELECT City.Name, COUNT(*)
FROM City
JOIN Person ON (Person.CityID = City.ID)
GROUP BY City.ID, City.Name

Cela devrait résoudre le problème.

La pratique rend parfait

Si vous ne comprenez toujours pas comment ou quand utiliser la clause GROUP BY ou si vous voulez vous entraîner à l'utiliser, consultez notre cours sur les requêtes SQL. Il couvre toutes les notions de base ainsi que les notions plus avancées, ce qui vous donnera de nombreuses occasions de perfectionner vos compétences en SQL.