Regroupement

• Fonctions d'agrégation
• Sommaire
• Exercices sur les agrégats

Vous savez donc que les fonctions d'agrégation groupent plusieurs lignes ensemble. Jusqu'à maintenant, toutes les lignes étaient chaque fois regroupées en une seule. Mais ce qui est particulièrement intéressant avec ces fonctions, c'est qu'il est possible de regrouper les lignes en fonction d'un critère, et d'avoir ainsi plusieurs groupes distincts. Par exemple, avec la fonction COUNT(*), vous pouvez compter le nombre de lignes que vous avez dans la table Animal. Mais que diriez-vous de faire des groupes par espèces, et donc de savoir en une seule requête combien vous avez de chats, chiens, etc. ? Un simple groupement, et c'est fait !

Au programme de ce chapitre :

• les règles et la syntaxe à appliquer pour regrouper des lignes ;
• le groupement sur plusieurs critères ;
• les "super-agrégats" ;
• la sélection de certains groupes sur la base de critères.

• Regroupement sur un critère
• Regroupement sur plusieurs critères
• Super-agrégats
• Conditions sur les fonctions d'agrégation

Regroupement sur un critère

Pour regrouper les lignes selon un critère, il faut utiliser GROUP BY, qui se place après l'éventuelle clause WHERE (sinon directement après FROM), suivi du nom de la colonne à utiliser comme critère de regroupement.

1
2
3
4

SELECT ...
FROM nom_table
[WHERE condition]
GROUP BY nom_colonne;

Exemple 1 : comptons les lignes dans la table Animal, en regroupant sur le critère de l'espèce (donc avec la colonne espece_id).

1
2
3

SELECT COUNT(*) AS nb_animaux
FROM Animal
GROUP BY espece_id;

Exemple 2 : Même chose, mais on ne prend que les mâles cette fois-ci.

1
2
3
4

SELECT COUNT(*) AS nb_males
FROM Animal
WHERE sexe = 'M'
GROUP BY espece_id;

C'est déjà intéressant, mais nous n'allons pas en rester là. En effet, il serait quand même mieux de savoir à quelle espèce correspond quel nombre !

Voir d'autres colonnes

Pour savoir à quoi correspond chaque nombre, il suffit d'afficher également le critère qui a permis de regrouper les lignes. Dans notre cas, espece_id.

1
2
3

SELECT espece_id, COUNT(*) AS nb_animaux
FROM Animal
GROUP BY espece_id;

C'est déjà mieux, mais l'idéal serait d'avoir le nom des espèces directement. Qu'à cela ne tienne, il suffit de faire une jointure ! Sans oublier de changer le critère et de mettre nom_courant à la place de espece_id.

1
2
3
4

SELECT nom_courant, COUNT(*) AS nb_animaux
FROM Animal
INNER JOIN Espece ON Animal.espece_id = Espece.id
GROUP BY nom_courant;

Colonnes sélectionnées

La règle SQL

Lorsque l'on fait un groupement dans une requête, avec GROUP BY, on ne peut sélectionner que deux types d'éléments dans la clause SELECT :

• une ou des colonnes ayant servi de critère pour le regroupement ;
• une fonction d'agrégation (agissant sur n'importe quelle colonne).

Cette règle est d'ailleurs logique. Imaginez la requête suivante :

1
2
3
4

SELECT nom_courant, COUNT(*) AS nb_animaux, date_naissance
FROM Animal
INNER JOIN Espece ON Animal.espece_id = Espece.id
GROUP BY nom_courant;

Que vient faire la date de naissance dans cette histoire ? Et surtout, quelle date de naissance espère-t-on sélectionner ? Chaque ligne représente une espèce puisque l'on a regroupé les lignes sur la base de Espece.nom_courant. Donc date_naissance n'a aucun sens par rapport aux groupes formés, une espèce n'ayant pas de date de naissance. Il en est de même pour les colonnes sexe ou commentaires par exemple.

Qu'en est-il des colonnes Espece.id, Animal.espece_id, ou Espece.nom_latin ? En groupant sur le nom courant de l'espèce, ces différentes colonnes ont un sens, et pourraient donc être utiles. Il a cependant été décidé que par sécurité, la sélection de colonnes n'étant pas dans les critères de groupement serait interdite. Cela afin d'éviter les situations comme au-dessus, où les colonnes sélectionnées n'ont aucun sens par rapport au groupement fait. Par conséquent, si vous voulez afficher également l'id de l'espèce et son nom latin, il vous faudra grouper sur les trois colonnes : Espece.nom_latin, Espece.nom_courant et Espece.id. Les groupes créés seront les mêmes qu'en groupant uniquement sur Espece.nom_courant, mais votre requête respectera les standards SQL.

1
2
3
4

SELECT Espece.id, nom_courant, nom_latin, COUNT(*) AS nb_animaux
FROM Animal
INNER JOIN Espece ON Animal.espece_id = Espece.id
GROUP BY nom_courant, Espece.id, nom_latin;

Le cas MySQL

On ne le répétera jamais assez : MySQL est un SGBD extrêmement permissif. Dans certains cas, c'est bien pratique, mais c'est toujours dangereux. Et notamment en ce qui concerne GROUP BY, MySQL ne sera pas perturbé pour un sou si vous sélectionnez une colonne qui n'est pas dans les critères de regroupement. Reprenons la requête qui sélectionne la colonne date_naissance alors que le regroupement se fait sur la base de l'espece_id. J'insiste, cette requête ne respecte pas la norme SQL, et n'a aucun sens. La plupart des SGBD vous renverront une erreur si vous tentez de l'exécuter.

1
2
3
4

SELECT nom_courant, COUNT(*) AS nb_animaux, date_naissance
FROM Animal
INNER JOIN Espece ON Animal.espece_id = Espece.id
GROUP BY espece_id;

Pourtant, loin de rouspéter, MySQL donne le résultat suivant :

MySQL a tout simplement pris n'importe quelle valeur parmi celles du groupe pour la date de naissance. D'ailleurs, il est tout à fait possible que vous ayez obtenu des valeurs différentes des miennes. Soyez donc très prudents lorsque vous utilisez GROUP BY. Vous faites peut-être des requêtes qui n'ont aucun sens, et MySQL ne vous en avertira pas !

Tri des données

Par défaut dans MySQL, les données sont triées sur la base du critère de regroupement. C'est la raison pour laquelle, dans la requête précédente, la colonne nom_courant est triée par ordre alphabétique : c'est le premier critère de regroupement. MySQL permet d'utiliser les mots-clés ASC et DESC dans une clause GROUP BY pour choisir un tri ascendant (par défaut) ou descendant.

1
2
3
4

SELECT Espece.id, nom_courant, nom_latin, COUNT(*) AS nb_animaux
FROM Animal
INNER JOIN Espece ON Animal.espece_id = Espece.id
GROUP BY nom_courant DESC, Espece.id, nom_latin;  -- On trie par ordre anti-alphabétique sur le nom_courant

Mais rien n'empêche d'utiliser une clause ORDER BY classique, après la clause GROUP BY. L'ORDER BY sera prioritaire sur l'ordre défini par la clause de regroupement.

Dans le même ordre d'idées, il n'est possible de faire un tri des données qu'à partir d'une colonne qui fait partie des critères de regroupement, ou à partir d'une fonction d'agrégation. Ça n'a pas plus de sens de trier les espèces par date de naissance que de sélectionner une date de naissance par espèce.

Vous pouvez par contre parfaitement écrire ceci :

1
2
3
4
5

SELECT Espece.id, nom_courant, nom_latin, COUNT(*) AS nb_animaux
FROM Animal
INNER JOIN Espece ON Animal.espece_id = Espece.id
GROUP BY nom_courant, Espece.id, nom_latin
ORDER BY nb_animaux;

Notez que la norme SQL veut que l'on n'utilise pas d'expressions (fonction, opération mathématique,…) dans GROUP BY ou ORDER BY. C'est la raison pour laquelle j'ai mis ORDER BY nb_animaux et non pas ORDER BY COUNT(*), bien qu'avec MySQL les deux fonctionnent. Pensez donc à utiliser des alias pour ces situations.

Et les autres espèces ?

La requête suivante nous donne le nombre d'animaux qu'on possède pour chaque espèce dont on possède au moins un animal. Comment peut-on faire pour afficher également les autres espèces ?

1
2
3
4

SELECT Espece.nom_courant, COUNT(*) AS nb_animaux
FROM Animal
INNER JOIN Espece ON Animal.espece_id = Espece.id
GROUP BY nom_courant;

Essayons donc avec une jointure externe, puisqu'il faut tenir compte de toutes les espèces, même celles qui n'ont pas de correspondance dans la table Animal.

1
2
3
4

SELECT Espece.nom_courant, COUNT(*) AS nb_animaux
FROM Animal
RIGHT JOIN Espece ON Animal.espece_id = Espece.id -- RIGHT puisque la table Espece est à droite.
GROUP BY nom_courant;

Les rats bruns apparaissent bien. En revanche, ce n'est pas 1 qu'on attend, mais 0, puisqu'on n'a pas de rats dans notre élevage. Cela dit, ce résultat est logique : avec la jointure externe, on aura une ligne correspondant aux rats bruns, avec NULL dans toutes les colonnes de la table Animal. Donc ce qu'il faudrait, c'est avoir les cinq espèces, mais ne compter que lorsqu'il y a un animal correspondant. Pour ce faire, il suffit de faire COUNT(Animal.espece_id) par exemple.

1
2
3
4

SELECT Espece.nom_courant, COUNT(Animal.espece_id) AS nb_animaux
FROM Animal
RIGHT JOIN Espece ON Animal.espece_id = Espece.id
GROUP BY nom_courant;

C'est pas magique ça ?

Regroupement sur plusieurs critères

J'ai mentionné le fait qu'il était possible de grouper sur plusieurs colonnes, mais jusqu'à maintenant, cela n'a servi qu'à pouvoir afficher correctement les colonnes voulues, sans que ça n'influe sur les groupes. On n'avait donc en fait qu'un seul critère, représenté par plusieurs colonnes. Voyons maintenant un exemple avec deux critères différents (qui ne créent pas les mêmes groupes).

Les deux requêtes suivantes permettent de savoir combien d'animaux de chaque espèce vous avez dans la table Animal, ainsi que combien de mâles et de femelles, toutes espèces confondues.

1
2
3
4
5
6
7
8

SELECT nom_courant, COUNT(*) as nb_animaux
FROM Animal
INNER JOIN Espece ON Espece.id = Animal.espece_id
GROUP BY nom_courant;

SELECT sexe, COUNT(*) as nb_animaux
FROM Animal
GROUP BY sexe;

En faisant un regroupement multicritère, il est possible de savoir facilement combien de mâles et de femelles par espèce il y a dans la table Animal. Notez que l'ordre des critères a son importance.

Exemple 1 : on regroupe d'abord sur l'espèce, puis sur le sexe.

1
2
3
4

SELECT nom_courant, sexe, COUNT(*) as nb_animaux
FROM Animal
INNER JOIN Espece ON Espece.id = Animal.espece_id
GROUP BY nom_courant, sexe;

Exemple 2 : on regroupe d'abord sur le sexe, puis sur l'espèce.

1
2
3
4

SELECT nom_courant, sexe, COUNT(*) as nb_animaux
FROM Animal
INNER JOIN Espece ON Espece.id = Animal.espece_id
GROUP BY sexe,nom_courant;

Étant donné que le regroupement par sexe donnait trois groupes différents, et le regroupement par espèce donnait quatre groupes différents, il peut y avoir jusqu'à douze (3 x 4) groupes lorsque l'on regroupe en se basant sur les deux critères. Ici, il y en aura moins puisque le sexe de tous les chiens et de tous les perroquets est défini (pas de NULL).

Super-agrégats

Parlons maintenant de l'option WITH ROLLUP de GROUP BY. Cette option va afficher des lignes supplémentaires dans la table de résultats. Ces lignes représenteront des "super-groupes" (ou super-agrégats), donc des "groupes de groupes". Deux petits exemples, et vous aurez compris !

Exemple avec un critère de regroupement

1
2
3
4

SELECT nom_courant, COUNT(*) as nb_animaux
FROM Animal
INNER JOIN Espece ON Espece.id = Animal.espece_id
GROUP BY nom_courant WITH ROLLUP;

Nous avons donc 20 chats, 21 chiens, 4 perroquets et 15 tortues. Et combien font 20 + 21 + 4 + 15 ? 60 ! Exactement. La ligne supplémentaire représente donc le regroupement de nos quatre groupes basé sur le critère GROUP BY nom_courant.

Exemple avec deux critères de regroupement

1
2
3
4
5

SELECT nom_courant, sexe, COUNT(*) as nb_animaux
FROM Animal
INNER JOIN Espece ON Espece.id = Animal.espece_id
WHERE sexe IS NOT NULL                            
GROUP BY nom_courant, sexe WITH ROLLUP;

Les deux premières lignes correspondent aux nombres de chats mâles et femelles. Jusque-là, rien de nouveau. Par contre, la troisième ligne est une ligne insérée par WITH ROLLUP, et contient le nombre de chats (mâles et femelles). Nous avons fait des groupes en séparant les espèces et les sexes, et WITH ROLLUP a créé des "super-groupes" en regroupant les sexes mais gardant les espèces séparées. Nous avons donc également le nombre de chiens à la sixième ligne, de perroquets à la neuvième, et de tortues à la douzième. Quant à la toute dernière ligne, c'est un "super-super-groupe" qui réunit tous les groupes ensemble.

C'est en utilisant WITH ROLLUP qu'on se rend compte que l'ordre des critères est vraiment important. En effet, voyons ce qui se passe si on échange les deux critères nom_courant et sexe.

1
2
3
4
5

SELECT nom_courant, sexe, COUNT(*) as nb_animaux
FROM Animal
INNER JOIN Espece ON Espece.id = Animal.espece_id
WHERE sexe IS NOT NULL
GROUP BY sexe, nom_courant WITH ROLLUP;

Cette fois-ci, les super-groupes ne correspondent pas aux espèces, mais aux sexes, c'est-à-dire au premier critère. Le regroupement se fait bien dans l'ordre donné par les critères.

NULL, c'est pas joli

Il est possible d'éviter d'avoir ces NULL dans les lignes des super-groupes. Pour cela, on peut utiliser la fonction COALESCE(). Cette fonction prend autant de paramètres que l'on veut, et renvoie le premier paramètre non NULL.

Exemples :

1
2
3

SELECT COALESCE(1, NULL, 3, 4); -- 1
SELECT COALESCE(NULL, 2);       -- 2
SELECT COALESCE(NULL, NULL, 3); -- 3

Voici comment l'utiliser dans le cas des super-agrégats.

1
2
3
4

SELECT COALESCE(nom_courant, 'Total'), COUNT(*) as nb_animaux
FROM Animal
INNER JOIN Espece ON Espece.id = Animal.espece_id
GROUP BY nom_courant WITH ROLLUP;

Tant qu'il s'agit de simples groupes, nom_courant contient bien le nom de l'espèce. COALESCE() renvoie donc celui-ci. Par contre, quand il s'agit du super-groupe, la colonne nom_courant du résultat contient NULL, et donc COALESCE() va renvoyer "Total".

Exemple : groupons sur le sexe, sans éliminer les lignes pour lesquelles le sexe n'est pas défini.

1
2
3
4

SELECT COALESCE(sexe, 'Total'), COUNT(*) as nb_animaux
FROM Animal
INNER JOIN Espece ON Espece.id = Animal.espece_id
GROUP BY sexe WITH ROLLUP;

Conditions sur les fonctions d'agrégation

Il n'est pas possible d'utiliser la clause WHERE pour faire des conditions sur une fonction d'agrégation. Donc, si l'on veut afficher les espèces dont on possède plus de 15 individus, la requête suivante ne fonctionnera pas.

1
2
3
4
5

SELECT nom_courant, COUNT(*)
FROM Animal
INNER JOIN Espece ON Espece.id = Animal.espece_id
WHERE COUNT(*) > 15
GROUP BY nom_courant;

1

ERROR 1111 (HY000): Invalid use of group function

Il faut utiliser une clause spéciale : HAVING. Cette clause se place juste après le GROUP BY.

1
2
3
4
5

SELECT nom_courant, COUNT(*)
FROM Animal
INNER JOIN Espece ON Espece.id = Animal.espece_id
GROUP BY nom_courant
HAVING COUNT(*) > 15;

Il est également possible d'utiliser un alias dans une condition HAVING :

1
2
3
4
5

SELECT nom_courant, COUNT(*) as nombre
FROM Animal
INNER JOIN Espece ON Espece.id = Animal.espece_id
GROUP BY nom_courant
HAVING nombre > 15;

Optimisation

Les conditions données dans la clause HAVING ne doivent pas nécessairement comporter une fonction d'agrégation. Les deux requêtes suivantes donneront par exemple des résultats équivalents :

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

SELECT nom_courant, COUNT(*) as nombre
FROM Animal
INNER JOIN Espece ON Espece.id = Animal.espece_id
GROUP BY nom_courant
HAVING nombre > 6 AND SUBSTRING(nom_courant, 1, 1) = 'C'; -- Deux conditions dans HAVING

SELECT nom_courant, COUNT(*) as nombre
FROM Animal
INNER JOIN Espece ON Espece.id = Animal.espece_id
WHERE SUBSTRING(nom_courant, 1, 1) = 'C'                  -- Une condition dans WHERE
GROUP BY nom_courant
HAVING nombre > 6;                                        -- Et une dans HAVING

Il est cependant préférable, et de loin, d'utiliser la clause WHERE autant que possible, c'est-à-dire pour toutes les conditions, sauf celles utilisant une fonction d'agrégation. En effet, les conditions HAVING ne sont absolument pas optimisées, au contraire des conditions WHERE.

En résumé

• Utiliser GROUP BY en combinaison avec une fonction d'agrégation permet de regrouper les lignes selon un ou plusieurs critères.
• Si l'on groupe sur un seul critère, on aura autant de groupes (donc de lignes) que de valeurs différentes dans la colonne utilisée comme critère.
• Si l'on groupe sur plusieurs critères, le nombre maximum de groupes résultant est obtenu en multipliant le nombre de valeurs différentes dans chacune des colonnes utilisées comme critère.
• Selon la norme SQL, si l'on utilise GROUP BY, on ne peut avoir dans la clause SELECT que des fonctions d'agrégation, ou des colonnes utilisées comme critère dans le GROUP BY.
• L'option WITH ROLLUP permet de créer des super-groupes (ou groupes de groupe).
• Une condition sur le résultat d'une fonction d'agrégation se place dans une clause HAVING, et non dans la clause WHERE.

• Fonctions d'agrégation
• Sommaire
• Exercices sur les agrégats