Démythifier le GROUP BY

Il existe une légende populaire à propos de la clause SQL GROUP BY. Cette légende soutient que la "norme SQL" impose, dans une requête, que les colonnes référencées dans la liste du SELECT apparaissent également dans la clause GROUP BY, à moins que ces colonnes n'apparaissent exclusivement dans une expression d'agrégation. MySQL est souvent accusé de violer cette norme.
Dans cet article, je vais tenter de briser ce mythe et d'apporter par la même occasion un regard plus mesuré sur le traitement du GROUP BY dans MySQL.
Pour ce faire, je vais d'abord démontrer que MySQL peut être configuré pour accepter des clauses GROUP BY qui ne comportent que des colonnes dans la liste du SELECT contenues dans des fonctions d'agrégation, rapprochant ainsi le comportement de MySQL de celui des autres célèbres SGBDR.
Ensuite, je vais montrer qu'il est très important de bien préciser à quelle version de la norme SQL on fait référence. Les deux versions les plus récentes présentent une définition plus sophistiquée de la relation entre les expressions contenues dans le GROUP BY et la liste du SELECT. Contrairement à une croyance populaire, ces normes n'imposent pas noir sur blanc que toutes les colonnes non contenues dans les fonctions d'agrégation de la liste du SELECT apparaissent dans la clause GROUP BY.
Enfin, j'emploierai un exemple simple mais concret pour illustrer de manière informelle ce que je pense que les dernières versions de la norme SQL essaient d'exprimer. Avec un peu de chance, j'arriverai à vous convaincre du bien-fondé de ne pas toujours inclure aveuglément toutes les colonnes non agrégées de la liste du SELECT dans la clause GROUP BY.
Avant de plonger dans le vif du sujet, je vais commencer par une brève présentation du GROUP BY, pour ceux qui ne sont pas familiers de cette notion. En introduction, je présenterai pourquoi la plupart des SGBD imposent que toutes les colonnes non agrégées et référencées dans la liste du SELECT apparaissent dans la clause GROUP BY, et pourquoi les utilisateurs rencontrent parfois des difficultés à cause de la façon dont MySQL traite la clause GROUP BY.

Allons au fait ! Qu'est-ce que la clause GROUP BY et que fait-elle ?
La clause GROUP BY est un élément optionnel des requêtes SQL SELECT. Syntaxiquement, la clause GROUP BY est constituée de la séquence de mots clefs GROUP BY, suivie d'une liste d'expressions (scalaires) séparées par des virgules. Si une requête SELECT contient une clause GROUP BY, celle-ci doit figurer après la clause WHERE (si la clause WHERE est omise, la clause GROUP BY suit immédiatement la clause FROM).
Quand elle est présente, la clause GROUP BY signifie que les données provenant d'un ensemble intermédiaire issu d'une requête doivent être divisées selon certains groupes, ne retournant qu'une seule ligne pour chacun de ces groupes. La liste des expressions précisées dans le GROUP BY définit la façon dont le regroupement doit se faire. Toutes les lignes présentant la même combinaison de valeurs pour toutes les expressions précisées dans le GROUP BY sont dans le même groupe.
Prenons comme exemple quelques requêtes simples pour illustrer les effets de la clause GROUP BY (sur ces exemples, j'utilise la table pet de la base de données menagerie). La requête suivante ramène toutes les lignes de la table pet :

SELECT  *
FROM    menagerie.pet;

La requête ramène un résultat qui ressemble à ceci :

+----------+--------+---------+------+------------+------------+
| name     | owner  | species | sex  | birth      | death      |
+----------+--------+---------+------+------------+------------+
| Fluffy   | Harold | cat     | f    | 1993-02-04 | NULL       |
| Claws    | Gwen   | cat     | m    | 1994-03-17 | NULL       |
| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL       |
| Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       |
| Bowser   | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |
| Chirpy   | Gwen   | bird    | f    | 1998-09-11 | NULL       |
| Whistler | Gwen   | bird    | NULL | 1997-12-09 | NULL       |
| Slim     | Benny  | snake   | m    | 1996-04-29 | NULL       |
| Puffball | Diane  | hamster | f    | 1999-03-30 | NULL       |
+----------+--------+---------+------+------------+------------+

(La clause ORDER BY n'ayant pas été précisée, les lignes sont renvoyées dans un ordre déterminé par la base de données, votre résultat pourrait être légèrement différent de celui ci-dessus. Cependant, pour cet exemple, seules les lignes importent, pas leur ordre).
Maintenant, supposons que l'on souhaite faire des groupes pour chaque espèce. L'ajout de la clause GROUP BY permet cela :

SELECT   species
FROM     menagerie.pet
GROUP BY species -- crée un groupe pour chaque espèce

Cette requête retourne le résultat suivant :

+---------+
| species |
+---------+
| bird    |
| cat     |
| dog     |
| hamster |
| snake   |
+---------+

A première vue, il semble que la clause GROUP BY ne fait rien de plus qu'un scan de chaque occurrence unique dans la colonne species et les renvoie. Toutefois, il vaut mieux considérer que chaque ligne du GROUP BY correspond à une ligne résumant un groupe de lignes qui ont la même valeur dans la colonne species. Ainsi, dans le cas présent, la ligne bird représente le groupe des oiseaux ("Chirpy" et "Wisthler") ; la ligne cat représente le groupe des chats ("Fluffy" et "Claws"), et ainsi de suite.

Une requête avec GROUP BY permet l'utilisation de fonctions d'agrégation sur une série de lignes réunies dans chaque groupe par la clause GROUP BY. Une fonction d'agrégation peut traiter des expressions pour chaque ligne dans un groupe de lignes afin de retourner une seule valeur. Quelques fonctions d'agrégation standard bien connues : COUNT, MIN, MAX et SUM (les fonctions d'agrégation peuvent également être employées sans clause GROUP BY, auquel cas le jeu de données intermédiaire est traité comme un seul grand groupe. Essayez d'imaginer l'effet d'une opération GROUP BY avec une liste GROUP BY vide : la requête ne va retourner qu'une seule ligne qui résume toutes les lignes du jeu de données intermédiaire).
Pour compléter notre exemple précédent avec la clause GROUP BY, l'exemple suivant montre l'effet de quelques fonctions d'agrégation :

SELECT   species
,        GROUP_CONCAT(name) -- crée une liste d'animaux par espèce
,        COUNT(*)           -- décompte les animaux par espèce
,        MIN(birth)         -- date de naissance du plus vieil animal par espèce
,        MAX(birth)         -- date de naissance du plus jeune animal par espèce
FROM     menagerie.pet
GROUP BY species

Cet exemple inclut également l'utilisation d'une fonction d'agrégation spécifique à MySQL, GROUP_CONCAT, qui va se révéler très pratique pour montrer les effets de la clause GROUP BY.
Le résultat ressemble à ceci :

+---------+--------------------+----------+------------+------------+
| species | GROUP_CONCAT(name) | COUNT(*) | MIN(birth) | MAX(birth) |
+---------+--------------------+----------+------------+------------+
| bird    | Chirpy,Whistler    |        2 | 1997-12-09 | 1998-09-11 |
| cat     | Fluffy,Claws       |        2 | 1993-02-04 | 1994-03-17 |
| dog     | Buffy,Fang,Bowser  |        3 | 1979-08-31 | 1990-08-27 |
| hamster | Puffball           |        1 | 1999-03-30 | 1999-03-30 |
| snake   | Slim               |        1 | 1996-04-29 | 1996-04-29 |
+---------+--------------------+----------+------------+------------+

Une fois de plus, on voit une ligne pour chaque groupe de lignes ayant la même valeur dans la colonne species, mais cette fois-ci, on peut également constater l'effet du traitement des lignes individuelles pour chaque espèce par une fonction d'agrégation :

• La fonction GROUP_CONCAT a été appliquée à la colonne name. Pour chaque espèce dans notre table d'animaux, il peut y avoir plusieurs animaux, et GROUP_CONCAT concatène leurs noms, séparant par défaut le nom de chacun par une virgule. Ainsi, dans cet exemple, l'expression GROUP_CONCAT fournit la constitution de chaque groupe d'animaux d'une même espèce ;
• La fonction COUNT est employée avec un joker *, lui demandant de compter le nombre de lignes associé à chaque groupe. On peut vérifier cela avec la colonne précédente et l'on voit immédiatement que le nombre de lignes au sein de chaque groupe est cohérent avec le nombre de noms concaténés par la fonction GROUP_CONCAT ;
• Les fonctions MIN et MAX sont appliquées à la colonne birth et ramène respectivement, pour chaque espèce, la date de naissance du plus vieil animal (MIN(birth) la date de naissance qui est plus petite que toutes les autres dates de naissance) et du plus jeune (MAX(birth) la date de naissance qui est plus grande que toutes les autres dates de naissance).

Les fonctions d'agrégation sont donc appliquées à des expressions issues d'un groupe de lignes, et ont pour effet de "résumer" (agréger) le groupe en une seule valeur. La plupart des fonctions d'agrégation calculent ou déterminent des statistiques qui permettent de caractériser le groupe comme un tout. La fonction GROUP_CONCAT, propre à MySQL, est une exception : elle énumère simplement tous les membres d'un groupe passé à la fonction, et ainsi n'est pas à proprement parler une fonction statistique. Toutefois, elle possède la principale propriété des fonctions d'agrégation, à savoir la capacité à transformer les expressions d'un groupe de lignes en une seule valeur.

Jusque là, nous avons vu quelques exemples avec GROUP BY parfaitement limpides. Il est pourtant facile de rencontrer des difficultés avec GROUP BY. Prenez l'exemple suivant :

SELECT   species
,        MIN(birth)  -- date de naissance du plus vieil animal par espèce
,        MAX(birth)  -- date de naissance du plus jeune animal par espèce
,        birth       -- date de naissance de ... oh oh...!
FROM     menagerie.pet

Cette requête ressemble à la précédente, où l'on avait calculé un certain nombre d'agrégats pour chaque groupe d'animaux appartenant à la même espèce. Cependant, cette fois-ci, on inclut aussi la colonne birth dans la liste du SELECT.
Si on tente de lancer cette requête sous Oracle, on récupère une erreur :

SQL> SELECT  species
 2  ,        MIN(birth)
 3  ,        MAX(birth)
 4  ,        birth
 5  FROM     sakila.pet
 6  GROUP BY species;
,       birth
       *
ERROR at line 4:
ORA-00979: not a GROUP BY expression

Le lancement de cette requête sous MySQL renvoie toutefois un résultat, qui ressemble à quelque chose comme :

SELECT   species
,        MIN(birth)  -- date de naissance du plus vieil animal par espèce
,        MAX(birth)  -- date de naissance du plus jeune animal par espèce
,        birth       -- date de naissance de ... oh oh...!
FROM     menagerie.pet

+---------+------------+------------+------------+
| species | MIN(birth) | MAX(birth) | birth      |
+---------+------------+------------+------------+
| bird    | 1997-12-09 | 1998-09-11 | 1998-09-11 |
| cat     | 1993-02-04 | 1994-03-17 | 1993-02-04 |
| dog     | 1979-08-31 | 1990-08-27 | 1989-05-13 |
| hamster | 1999-03-30 | 1999-03-30 | 1999-03-30 |
| snake   | 1996-04-29 | 1996-04-29 | 1996-04-29 |
+---------+------------+------------+------------+
5 rows in set (0.00 sec)

Que se passe-t-il donc ? Pourquoi une telle différence ? En fait, comment MySQL se comporte-t-il dans le cas présent ? Parfois, la colonne birth ramène une ligne qui a l'air d'être sa valeur maximale au sein de l'espèce (première ligne), et parfois on voit la valeur minimale (deuxième ligne). On trouve même un cas où la valeur retournée se situe entre le minimum et le maximum (ligne 3). Comment expliquer un comportement apparemment aussi aléatoire ?

Il n'est pas trop compliqué de comprendre ce qui se passe ici. Tout ce que nous avons à faire, c'est de revenir à notre explication de l'effet de la clause GROUP BY, et regarder comment elle s'applique à notre dernière requête.
Nous avons déjà expliqué que la clause GROUP BY ramenait une ligne de chaque groupe de lignes présentes dans le résultat intermédiaire, et que les groupes sont déterminés par la liste définie dans la clause GROUP BY. Ainsi, dans le cas présent, nous créons une ligne de résultats pour chaque groupe de lignes qui appartiennent à la même espèce, puisque la liste du GROUP BY ne contient que la colonne species. Mais comme il y a plusieurs animaux qui peuvent appartenir à une espèce donnée, la colonne birth peut avoir (et a souvent) une valeur différente pour chaque ligne dans un groupe d'espèce donné.
Une fois qu'on a compris cela, on est en droit de se demander : puisqu'il y a plusieurs valeurs dans la colonne birth pour une espèce donnée, laquelle sera retournée ? Qu'a-t-on fait en spécifiant la colonne birth dans la liste du SELECT ?
Il n'y a pas de réponse juste à cette question. Il est certainement possible de ne sélectionner qu'une seule valeur parmi toutes celles de la colonne birth : en fait, c'est exactement ce que fait MySQL. Cependant, il est impossible de définir laquelle des valeurs possibles est choisie. En fait, ça n'a pas de sens de vouloir mélanger les valeurs des lignes qui appartiennent à un groupe avec le groupe lui-même. C'est pourquoi ça n'a pas non plus de sens de faire figurer la colonne birth dans la liste du SELECT.
Une autre manière de considérer la question revient à dire que le niveau des valeurs des espèces est différent (et par-là même incompatible) avec le niveau des valeurs de birth. Ca ne signifie pas pour autant qu'on ne puisse pas accéder aux valeurs de la colonne birth, ça signifie plutôt que l'on doit utiliser une fonction d'agrégation pour récupérer la "bonne" valeur dans le groupe de ces valeurs.

Qu'en est-il d'un comportement comme celui d'Oracle ? Est-ce que ça a plus de sens de renvoyer un message d'erreur plutôt que de retourner une donnée qui n'a pas de sens ? Dans un certain sens, la plupart des gens seront d'accord. Bien entendu, le message d'erreur lui-même est plutôt surprenant :

,       birth
       *
ERROR at line 4:
ORA-00979: not a GROUP BY expression

Il semble suggérer que le problème provient du fait que la colonne birth dans la liste du SELECT n'est pas incluse dans la clause GROUP BY. A son tour, cela soulève la question de savoir si le problème serait résolu en incluant la colonne birth dans la clause GROUP BY.
Le fait d'ajouter la colonne birth dans la clause GROUP BY permet certainement de se débarrasser du message d'erreur. Cependant, beaucoup d'utilisateurs débutants dans le SQL ne comprendraient pas que cela produit une requête différente. Le GROUP BY species original produit un groupe pour chaque espèce, alors que GROUP BY species, birth crée un groupe pour chaque combinaison de valeurs species et birth (ne correspondant sûrement pas à ce qui est voulu).
D'un autre côté, on ne peut pas attendre du SGBD qu'il sache ce à quoi on pensait en incluant birth dans la liste du SELECT en premier lieu. Donc, bien que le message d'erreur puisse sembler surprenant, il est toujours préférable que de retourner des données sans aucun sens, en silence.
Sommes-nous vraiment obligé de supporter cela ? La réponse est « non » !

Désormais, MySQL est également capable de détecter ce problème, et il est parfaitement possible de faire rejeter la requête précédente par MySQL et ainsi éviter qu'il ne renvoie des données sans aucun sens. C'est faisable en incluant ONLY_FULL_GROUP_BY dans le sql_mode.
Comme la plupart des réglages de serveur, on peut préciser le sql_mode en passant l'argument de ligne de commande --sql-mode à l'exécutable du serveur MySQL (mysqld), ou on peut l'inclure dans un fichier d'options. Par exemple, inclure la ligne suivante dans le fichier d'options activera ONLY_FULL_GROUP_BY au démarrage du serveur :

sql_mode=ONLY_FULL_GROUP_BY

Depuis MySQL 4.1, il est également possible d'ajuster le sql_mode en cours d'exécution par le biais de la syntaxe SET. De cette façon, le sql_mode peut être ajusté globalement ou au niveau de la session. Ce dernier est le plus utile, parce qu'il permet à chacun d'ajuster au mieux le sql_mode pour une application sans affecter les autres applications qui tournent sur le serveur (certaines applications exigent les paramètres par défaut et peuvent ne plus fonctionner avec un sql_mode particulier).
Le fragment de code suivant montre comment inclure ONLY_FULL_GROUP_BY dans le sql_mode en cours d'exécution :

mysql> SET sql_mode := CONCAT('ONLY_FULL_GROUP_BY,',@@sql_mode);
Query OK, 0 rows affected (0.00 sec)

La variable serveur @@sql_mode contient une chaîne de caractères (éventuellement vide) des paramètres du sql_mode, séparés par une virgule. La fonction CONCAT ajoute en début de sql_mode ONLY_FULL_GROUP_BY, quoi que la chaîne contienne déjà. Remarquez la virgule juste après ONLY_FULL_GROUP_BY. Si la valeur de la variable @@sql_mode est une chaîne vide, la valeur issue de l'expression CONCAT se terminera par une virgule, ce qui est autorisé (les virgules finales sont supprimées dans le processus).
Si on tente d'exécuter de nouveau notre requête, elle échoue avec un message d'erreur :

mysql> SELECT   species
    -> ,        MIN(birth)  -- birthdate of oldest pet per species
    -> ,        MAX(birth)  -- birthdate of youngest pet per species
    -> ,        birth       -- birthdate of ... uh oh...!
    -> FROM     menagerie.pet
    -> GROUP BY species;
ERROR 1055 (42000): 'menagerie.pet.birth' isn't in GROUP BY

Le message d'erreur indique qu'on n'a pas inclus la colonne birth dans la clause GROUP BY. Désormais, MySQL se comporte de la même manière qu'Oracle avec cette requête.

Dans les parties précédentes, nous avons vu qu'Oracle et MySQL réagissent de façon très différente à la clause GROUP BY. Mais que dit la norme ? Comment la clause GROUP BY est-elle supposée se comporter ?
En introduction de cet article, j'ai clamé que j'allais briser un mythe populaire qui prétend que... la norme SQL impose que les colonnes référencées dans la liste du SELECT apparaissent également dans la clause GROUP BY, à moins que ces colonnes apparaissent exclusivement dans une fonction d'agrégation.
Maintenant, je ne prétends pas être un expert de la norme SQL (ISO/IEC 9075). En fait, j'ai noté à plusieurs reprises que le volume de documentation, de même que le vocabulaire très soutenu ne me permettent pas d'avoir une vue claire de la question. Mais essayons tout de même.
Dans la version de 1992 de la norme, le paragraphe 7.9-7 stipule :

"Si T est une table regroupée, alors toute <colonne référence> dans chaque <expression> qui référence une colonne de T doit référencer un groupement de colonnes ou être spécifié dans un <ensemble de fonctions de spécification>."

Comme je le disais, je ne suis pas un spécialiste de la question, mais tel que je le lis, cela se résume à :

"Les requêtes qui contiennent une clause GROUP BY peuvent inclure une colonne référencée dans le SELECT uniquement si la colonne apparaît dans la clause GROUP BY, ou si cette colonne apparaît dans une fonction d'agrégation."

Désormais, au paragraphe 7.12-15 de la norme de 2003, on trouve ceci :

"Soient T une table groupée et G l'ensemble des colonnes groupées sur T. Pour toute <expression> contenue dans la <liste du SELECT>, toute référence de colonne qui référence une colonne de T doit référencer une colonne C qui dépend fonctionnellement de G ou qui doit être contenue dans un argument agrégé d'un <ensemble de fonctions de spécification> dont la requête d'agrégation est QS."

La version de la norme de 1999 contient une règle identique. Ce qu'il est important de retenir ici, c'est que les 2 versions qui succédèrent à la norme de 1992 ne requièrent plus explicitement que toutes les colonnes non agrégées dans la liste du SELECT soient présentes dans la clause GROUP BY. A la place, il faut que toute colonne non agrégée qui apparaît dans la liste du SELECT soit fonctionnellement dépendante de la clause GROUP BY.

Que peut bien vouloir dire « fonctionnellement dépendant » dans les normes de 1999 et 2003 ? La réponse à cette question est également définie dans la norme. Malheureusement, on ne peut pas l'illustrer par un exemple simple, parce que la définition formelle de ce que constitue exactement une dépendance fonctionnelle, selon la norme, est plutôt vaste et compliqué.
Par chance, le concept de dépendance fonctionnelle peut être illustré simplement de manière un peu moins formelle. Supposons que l'on ait deux expressions, A et B. B est dépendant fonctionnellement de A si B a une valeur et une seule pour une valeur particulière de A. Regardons l'extrait de code suivant :

mysql> SELECT @A:=1      AS A
    -> ,      @B:=@A + 1 AS B;
+---+------+
| A | B    |
+---+------+
| 1 |    2 |
+---+------+

Ici, la colonne B est dépendante fonctionnellement de la colonne A. La valeur de B peut être déduite de la valeur de A de manière très étroite, à savoir en ajoutant 1 à toute valeur de A. On peut calculer la valeur de B quelle que soit la valeur de A, et pour toute valeur de A, la valeur correspondante de B sera toujours la même.
La notion de dépendance fonctionnelle peut aussi s'appliquer à plusieurs colonnes :

SELECT CONCAT(A,B) C
FROM   uneTable

La colonne C est définie par le résultat de la fonction CONCAT(A,B). Connaissant les valeurs de A et de B, on peut calculer le résultat de CONCAT(A,B). Bien entendu, pour un couple donné de valeurs de A et B, le résultat de CONCAT(A,B) sera toujours le même. Ainsi, C est dépendant fonctionnellement du couple de colonnes A et B.
Notez qu'il ne suffit pas simplement de connaître la fonction de calcul de B à partir de A. Prenons cet exemple :

mysql> SELECT @A:=1           AS A
    -> ,      @B:=@A + RAND() AS B;

Ici, nous connaissons le moyen d'obtenir la valeur de B à partir de la valeur de A : on prend la valeur de A et on lui ajoute la valeur renvoyée par la fonction RAND(). Toutefois, la fonction RAND() retourne une valeur différente à chaque fois qu'on l'appelle. De ce fait, la valeur de B sera différente à chaque fois, et on peut donc en conclure que B n'est pas dépendant fonctionnellement de A. Ainsi, il n'y a pas une valeur unique de B pour une même valeur de A, donc B ne dépend pas fonctionnellement de A.

8-A. Dépendance fonctionnelle et normalisation▲

8-B. Dépendances fonctionnelles et GROUP BY▲

mysql> SELECT   film_id       -- clé primaire
    -> ,        title         -- colonne non-clé
    -> ,        COUNT(*)      -- une ligne par groupe
    -> FROM     sakila.film
    -> GROUP BY film_id;      -- group by sur la clé primaire
+---------+-----------------------------+----------+
| film_id | title                       | COUNT(*) |
+---------+-----------------------------+----------+
|       1 | ACADEMY DINOSAUR            |        1 |
.         .                             .          .
.         .                             .          .
[...on ne montre pas les 998 lignes...]
.         .                             .          .
.         .                             .          .
|    1000 | ZORRO ARK                   |        1 |
+---------+-----------------------------+----------+
1000 rows in set (0.05 sec)

mysql> SELECT    f.film_id
    -> ,         f.title
    -> ,         COUNT(fa.actor_id)
    -> FROM      film        f
    -> LEFT JOIN film_actor  fa
    -> ON        f.film_id = fa.film_id
    -> GROUP BY  f.film_id;
+---------+-----------------------------+----------+
| film_id | title                       | COUNT(*) |
+---------+-----------------------------+----------+
|       1 | ACADEMY DINOSAUR            |       10 |
.         .                             .          .
.         .                             .          .
[...on ne montre pas les 998 lignes...]
.         .                             .          .
.         .                             .          .
|    1000 | ZORRO ARK                   |        3 |
+---------+-----------------------------+----------+
1000 rows in set (0.02 sec)

Je l'espère, j'ai été capable d'expliquer dans quelles circonstances il n'y a pas de risque, dans les requêtes avec GROUP BY, de référencer directement des colonnes dans la liste du SELECT. On pourrait pourtant se demander quels en sont les avantages et les inconvénients. Je veux dire, pouvoir le faire ne signifie pas qu'on doive le faire, non ?

9-A. Inconvénients ?▲

9-B. Avantages▲

mysql> SELECT    f.film_id
    -> ,         f.title
    -> ,         sum(p.amount) sum_amount
    -> FROM      film f
    -> LEFT JOIN inventory i
    -> ON        f.film_id = i.film_id
    -> LEFT JOIN rental r
    -> ON        i.inventory_id = r.inventory_id
    -> LEFT JOIN payment p
    -> ON        r.rental_id = p.rental_id
    -> GROUP BY  f.film_id
    -> HAVING    sum_amount > 300;
Empty set (0.18 sec)

mysql> SELECT    f.film_id
    -> ,         f.title
    -> ,         sum(p.amount) sum_amount
    -> FROM      film f
    -> LEFT JOIN inventory i
    -> ON        f.film_id = i.film_id
    -> LEFT JOIN rental r
    -> ON        i.inventory_id = r.inventory_id
    -> LEFT JOIN payment p
    -> ON        r.rental_id = p.rental_id
    -> GROUP BY  f.film_id
    -> ,         f.title
    -> HAVING    sum_amount > 300;
Empty set (0.51 sec)

mysql> EXPLAIN
    -> SELECT    f.film_id
    -> ,         f.title
    -> ,         sum(p.amount) sum_amount
    -> FROM      film f
    -> LEFT JOIN inventory i
    -> ON        f.film_id = i.film_id
    -> LEFT JOIN rental r
    -> ON        i.inventory_id = r.inventory_id
    -> LEFT JOIN payment p
    -> ON        r.rental_id = p.rental_id
    -> GROUP BY  f.film_id
    -> HAVING    sum_amount > 300
    -> \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: f
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 2
          ref: NULL
         rows: 953
        Extra:
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: i
         type: ref
possible_keys: idx_fk_film_id
          key: idx_fk_film_id
      key_len: 2
          ref: sakila.f.film_id
         rows: 2
        Extra: Using index
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: r
         type: ref
possible_keys: idx_fk_inventory_id
          key: idx_fk_inventory_id
      key_len: 3
          ref: sakila.i.inventory_id
         rows: 1
        Extra: Using index
*************************** 4. row ***************************
           id: 1
  select_type: SIMPLE
        table: p
         type: ref
possible_keys: fk_payment_rental
          key: fk_payment_rental
      key_len: 5
          ref: sakila.r.rental_id
         rows: 1
        Extra:
4 rows in set (0.00 sec)

mysql> EXPLAIN
    -> SELECT    f.film_id
    -> ,         f.title
    -> ,         sum(p.amount) sum_amount
    -> FROM      film f
    -> LEFT JOIN inventory i
    -> ON        f.film_id = i.film_id
    -> LEFT JOIN rental r
    -> ON        i.inventory_id = r.inventory_id
    -> LEFT JOIN payment p
    -> ON        r.rental_id = p.rental_id
    -> GROUP BY  f.film_id
    -> ,         f.title
    -> HAVING    sum_amount > 300
    -> \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: f
         type: index
possible_keys: NULL
          key: idx_title
      key_len: 767
          ref: NULL
         rows: 953
        Extra: Using index; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: i
         type: ref
possible_keys: idx_fk_film_id
          key: idx_fk_film_id
      key_len: 2
          ref: sakila.f.film_id
         rows: 2
        Extra: Using index
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: r
         type: ref
possible_keys: idx_fk_inventory_id
          key: idx_fk_inventory_id
      key_len: 3
          ref: sakila.i.inventory_id
         rows: 1
        Extra: Using index
*************************** 4. row ***************************
           id: 1
  select_type: SIMPLE
        table: p
         type: ref
possible_keys: fk_payment_rental
          key: fk_payment_rental
      key_len: 5
          ref: sakila.r.rental_id
         rows: 1
        Extra:
4 rows in set (0.01 sec)

9-C. Agrégation sur des colonnes fonctionnellement dépendantes▲

mysql> SELECT    f.film_id
    -> ,         MAX(f.title) AS title
    -> ,         sum(p.amount) sum_amount
    -> FROM      film f
    -> LEFT JOIN inventory i
    -> ON        f.film_id = i.film_id
    -> LEFT JOIN rental r
    -> ON        i.inventory_id = r.inventory_id
    -> LEFT JOIN payment p
    -> ON        r.rental_id = p.rental_id
    -> GROUP BY  f.film_id
    -> HAVING    sum_amount > 300;
Empty set (0.20 sec)

Contrairement à une croyance populaire, la norme SQL n'impose pas que les requêtes avec GROUP BY référencent toutes les colonnes non agrégées de la liste du SELECT dans la clause GROUP BY. A l'instar de la version de 1999 de la norme SQL, il est explicitement permis que la liste du SELECT référence des expressions non agrégées, du moment qu'elles dépendent fonctionnellement de la liste du GROUP BY.
Toute expression qui n'a qu'une seule valeur pour chaque groupe défini dans la clause GROUP BY dépend fonctionnellement de cette clause. On peut percevoir les dépendances fonctionnelles selon un motif de requête courant : chaque fois qu'on a une jointure entre une table maître et une table détail pour calculer des agrégats sur les lignes de détail selon chaque ligne du maître, on peut utiliser la clause GROUP BY sur la clé primaire ou unique du maître. Toute colonne non clé d'une ligne du maître sera dépendante fonctionnellement de la clé, et peut ainsi figurer dans la liste du SELECT, en dehors d'une fonction d'agrégation.
Dans MySQL, il est possible d'écrire des requêtes avec GROUP BY qui référencent des colonnes dans la liste du SELECT non incluses dans la clause du GROUP BY, même si ces colonnes ne dépendent pas fonctionnellement de la clause GROUP BY. Ce comportement n'est conforme à aucune des versions de la norme SQL. Il est possible de l'éviter en incluant ONLY_FULL_GROUP_BY dans le paramètre sql_mode du serveur, mais il peut être plus judicieux de tirer parti de cette possibilité pour écrire des clauses GROUP BY partielles.
En résumé :

• Il n'y a absolument aucun risque avec des clauses GROUP BY partielles, du moment que les colonnes non agrégées dans la liste du SELECT dépendent fonctionnellement de la clause GROUP BY.
• Un GROUP BY partiel peut apporter de meilleures performances, puisqu'il évite au serveur l'évaluation de toute la liste du GROUP BY.
• Si on ne souhaite pas écrire une clause GROUP BY partielle, il est préférable d'utiliser MIN ou MAX pour agréger les colonnes fonctionnellement dépendantes dans la liste du SELECT plutôt que de déplacer ces colonnes dans la clause GROUP BY.