En SQL standard, une requête qui inclut une clause GROUP BY ne peut pas faire référence à des colonnes non agrégées dans la liste de sélection qui ne sont pas nommés dans la clause GROUP BY. Par exemple, cette requête est illégale en SQL standard car la colonne non agrégée name de la liste de sélection n’apparaît pas dans la GROUP BY:

SELECT o.custid, c.name, MAX(o.payment) FROM orders AS o, customers AS c WHERE o.custid = c.custid GROUP BY o.custid;

Pour que la requête soit légale, la colonne name doit être omise de la liste select ou nommée dans la clause GROUP BY.

MySQL étend l’utilisation SQL standard de GROUP BY afin que la liste de sélection puisse faire référence à des colonnes non agrégées non nommées dans la clause GROUP BY. Cela signifie que la requête précédente est légale dans MySQL. Vous pouvez utiliser cette fonctionnalité pour obtenir de meilleures performances en évitant le tri et le regroupement inutiles des colonnes. Cependant, cela est utile principalement lorsque toutes les valeurs de chaque colonne non agrégée non nommée dans GROUP BY sont les mêmes pour chaque groupe. Le serveur est libre de choisir n’importe quelle valeur de chaque groupe, donc à moins qu’elles ne soient identiques, les valeurs choisies ne sont pas déterministes. De plus, la sélection des valeurs de chaque groupe ne peut pas être influencée par l’ajout d’une clause ORDER BY. Le tri des ensembles de résultats se produit après le choix des valeurs, et ORDER BY n’affecte pas les valeurs de chaque groupe choisies par le serveur.

Une extension MySQL similaire s’applique à la clause HAVING. En SQL standard, une requête ne peut pas faire référence à des colonnes non agrégées de la clause HAVING qui ne sont pas nommées dans la clause GROUP BY. Pour simplifier les calculs, une extension MySQL permet des références à de telles colonnes. Cette extension suppose que les colonnes non groupées ont les mêmes valeurs par groupe. Sinon, le résultat est non déterministe.

Pour désactiver l’extension MySQL GROUP BY et activer le comportement SQL standard, activez le mode SQL ONLY_FULL_GROUP_BY. Dans ce cas, les colonnes non nommées dans la clause GROUP BY ne peuvent pas être utilisées dans la clause select list ou HAVING sauf si elles sont incluses dans une fonction d’agrégation.

L’extension select list s’applique également à ORDER BY. Autrement dit, vous pouvez faire référence à des colonnes non agrégées dans la clause ORDER BY qui n’apparaissent pas dans la clause GROUP BY. (Cependant, comme mentionné précédemment, ORDER BY n’affecte pas les valeurs choisies parmi les colonnes non agrégées ; il ne les trie qu’après avoir été choisies.) Cette extension ne s’applique pas si le mode SQL ONLY_FULL_GROUP_BY est activé.

Si une requête a des fonctions d’agrégation et aucune clause GROUP BY, elle ne peut pas avoir de colonnes non agrégées dans la liste de sélection, de condition HAVING, ou de liste ORDER BY avec ONLY_FULL_GROUP_BY activée:

mysql> SELECT name, MAX(age) FROM t;ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...)with no GROUP columns is illegal if there is no GROUP BY clause

Sans GROUP BY, il existe un seul groupe et il n’est pas déterministe quelle namevaleur à choisir pour le groupe.

Une autre extension MySQL à SQL standard permet des références dans la clause HAVING aux expressions aliasées de la liste select. L’activation de ONLY_FULL_GROUP_BY empêche cela. Par exemple, la requête suivante renvoie des valeurs name qui n’apparaissent qu’une seule fois dans la table orders; la requête est acceptée indépendamment du fait que ONLY_FULL_GROUP_BY soit activée :

SELECT name, COUNT(name) FROM orders GROUP BY name HAVING COUNT(name) = 1;

La requête suivante n’est acceptée que si ONLY_FULL_GROUP_BY est désactivée.

SELECT name, COUNT(name) AS c FROM orders GROUP BY name HAVING c = 1;

Si vous essayez de suivre SQL standard, vous ne pouvez utiliser que des expressions de colonne dans les clauses GROUP BY. Pour contourner le problème, utilisez un alias pour l’expression :

SELECT id, FLOOR(value/100) AS val FROM tbl_name GROUP BY id, val;

MySQL autorise les expressions sans colonne dans les clauses GROUP BY, l’alias n’est donc pas nécessaire :

SELECT id, FLOOR(value/100) FROM tbl_name GROUP BY id, FLOOR(value/100);