7 erreurs courantes de GROUP BY

Vous commettez ces erreurs avec GROUP BY en SQL ? Découvrez ce qu'elles sont, comment les éviter et comment les corriger.

L'instruction SQL GROUP BY peut permettre de découvrir rapidement des informations importantes sur les données. Au début, l'utilisation de GROUP BY peut sembler facile, par exemple lors de la création de rapports SQL de base que vous présenterez aux décideurs de l'entreprise. Mais en apprenant cette puissante fonctionnalité, vous pourriez vous retrouver piégé par des erreurs bizarres ou obtenir des résultats incorrects causés par des instructions GROUP BY mal rédigées. Si vous avez l'impression que les choses ne tournent pas rond dans votre utilisation de GROUP BY, poursuivez votre lecture. Dans cet article, je vais vous expliquer les erreurs les plus courantes de GROUP BY et comment vous pouvez les éviter.

Ne commettez plus ces 7 erreurs courantes de GROUP BY

1. Oublier GROUP BY avec les fonctions d'agrégation

Vous utilisez les instructions SELECT avec la clause GROUP BY lorsque vous souhaitez regrouper et organiser des lignes en groupes spécifiques, puis effectuer un calcul spécifique sur chaque groupe.

L'erreur la plus courante de GROUP BY est d'oublier d'écrire GROUP BY à l'intérieur de l'instruction SELECT.

Voici un exemple. Imaginez que vous avez la table recettesqui contient 100 enregistrements et six colonnes. Cette table stocke le nombre de vues (no_of_views) pour chaque recette publiée sur un célèbre site web culinaire :

tableau des recettes

Voici une brève description des colonnes de ce tableau :

• meal_category - La catégorie de la recette (soupe, boissons, desserts, etc.).
• name - Le nom de la recette.
• author - Le nom de l'auteur.
• no_of_views - Le nombre de vues (total des pages/recettes vues) dans le mois en cours.
• no_of_views_lst_mth - Le nombre de consultations (total des pages/recettes consultées) au cours du mois précédent.
• author_id - Le numéro d'identification unique de l'auteur.

Supposons que vous souhaitiez compter le nombre de recettes dans chaque catégorie de repas. Si vous écrivez la déclaration comme ceci (sans GROUP BY à la fin) ...

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes ;

... la plupart des moteurs SQL vous donneront une erreur. Certains moteurs, cependant, produiront des résultats bizarres et indésirables. J'utilise MySQL et lorsque j'exécute cette instruction, j'obtiens ceci :

Résultat sans GROUP BY

100 est le nombre total de recettes dans l'ensemble des données et la catégorie de repas "Entrée froide" n'est qu'une catégorie sur dix. Pour corriger ce type d'erreur, vous devez ajouter un GROUP BY meal_category à la fin de l'instruction. (Sinon, votre résultat dans MySQL n'a tout simplement aucun sens).

Le SELECT correct ressemble à ceci :

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes
GROUP BY meal_category ;

Voici une brève explication de ce qui se passe :

• Les enregistrements sont fusionnés en fonction de la catégorie de repas. Par exemple, les desserts constituent un groupe, les soupes un autre, les plats principaux un autre encore, etc. La colonne meal_category est spécifiée après GROUP BY; elle figure également dans SELECT.
• Pour chaque groupe, nous utilisons COUNT(*) pour compter le nombre total de recettes dans ce groupe.

Je ne vais pas m'attarder sur la syntaxe ici, mais je vous conseille vivement de lire Explication de GROUP BY en SQL ou Utilisation de GROUP BY en SQL pour plus de détails.

Comme vous pouvez le constater, le résultat est conforme à nos attentes :

Résultat valide de GROUP BY

2. Confusion entre WHERE et HAVING

Vous aimeriez peut-être voir uniquement les catégories de repas qui contiennent plus de 10 recettes. Beaucoup de débutants écriraient cette requête :

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes
WHERE count(*) > 10 
GROUP BY meal_category ;

Cette instruction renvoie une erreur car vous ne pouvez pas utiliser de fonctions d'agrégation dans une clause WHERE. WHERE est utilisé avec GROUP BY lorsque vous souhaitez filtrer les lignes avant de les regrouper.

Dans notre exemple, nous voulons filtrer les lignes après les avoir regroupées; dans des cas comme celui-ci, nous devons utiliser la clause HAVING:

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes
           GROUP BY meal_category
HAVING count(*) > 10  ;

Ce malentendu sur la différence entre HAVING et WHERE est la deuxième erreur la plus courante avec GROUP BY.

Clarifions cette différence à l'aide de deux autres exemples.

Exemple 1 - Comment afficher les catégories de repas avec 1M+ de vues

Une déclaration qui affiche uniquement les catégories ayant plus d'un million de pages vues au total peut être écrite comme ceci :

SELECT 
  meal_category,
  sum(no_of_views) AS total 
FROM recipes 
GROUP BY meal_category
HAVING sum(no_of_views) >1000000;

Ici, nous utilisons HAVING parce que nous voulons filtrer les enregistrements après qu'ils aient été regroupés. Le résultat est présenté ci-dessous :

Exemple avec HAVING

Exemple 2 - Performances de Jean dans chaque catégorie de repas

Cette requête extrait uniquement les recettes de Jean et calcule ses performances :

SELECT 
  meal_category, 
  sum(no_of_views) AS total 
FROM recipes 
WHERE author = ‘John’ 
GROUP BY meal_category;

Nous utilisons WHERE parce que nous devons filtrer les enregistrements (afin de n'obtenir que les données de John) avant de placer les enregistrements dans des groupes par catégorie de repas. Voici à quoi ressemble le résultat :

Les KPIs de John

HAVING et WHERE sont joliment décrits dans nos articles What Is the Difference Between WHERE and HAVING Clauses in SQL ? et 5 Exemples de GROUP BY. Si vous souhaitez voir plus d'exemples sur ce sujet, je vous suggère de commencer par là.

3. Énumération d'une colonne dans SELECT mais pas dans GROUP BY

Supposons maintenant que vous vouliez voir le nombre total de vues par meal_category et author. Nous pouvons le faire - ?il suffit d'ajouter la colonne auteur à notre requête précédente :

SELECT 
  meal_category,
  author,
  sum(no_of_views) AS total 
FROM recipes 
GROUP BY meal_category;

Bien sûr que non, cela entraînera une erreur dans la plupart des moteurs SQL. Par exemple, Oracle vous dira "erreur: Not a GROUP BY expression". Pourquoi cette erreur déroutante ? Que manque-t-il ici ?

Eh bien, le moteur SQL ne sait pas comment calculer le total pour chaque auteur parce que nous ne l'avons pas inclus dans la clause GROUP BY ; l'attribut author ne figure pas dans la clause GROUP BY. C'est une autre erreur courante avec GROUP BY.

Corrigeons cette requête et exécutons-la une nouvelle fois :

SELECT 
  meal_category,
  author,
  sum(no_of_views) AS total 
FROM recipes 
GROUP BY meal_category, author;

Le résultat est le suivant :

Maintenant, cela semble correct. N'oubliez pas que les colonnes non agrégées qui figurent dans SELECT doivent également figurer dans .GROUP BYDans notre cas, les colonnes non agrégées sont meal_category et author, qui sont maintenant dans SELECT et GROUP BY.

Vous ne répertoriez pas les colonnes qui se trouvent dans les fonctions d'agrégation dans GROUP BY. Dans notre exemple, la colonne no_of_views est utilisée dans la fonction d'agrégation SUM() et n'est donc pas répertoriée dans la clause GROUP BY.

Si vous souhaitez en savoir plus sur ce sujet, consultez notre article intitulé GROUP BY Clause : How Well Do You Know It ? Il explique pourquoi les colonnes SÉLECTÉES doivent apparaître dans la clause GROUP BY. En outre, l'article Comment corriger une erreur "Not a GROUP BY Expression" donne d'autres exemples liés à ce type d'erreur.

4. Pas de regroupement par une clé unique

Essayons maintenant autre chose. Supposons que nous voulions obtenir le nombre moyen de pages vues pour chaque auteur de recette. La requête suivante calcule le nombre total moyen de pages vues pour chaque auteur en utilisant le nom de l'auteur :

SELECT 
  author,
  avg(no_of_views) 
FROM recipes 
GROUP BY author;

En regardant le résultat, vous remarquerez que Lisa a une moyenne de 116101,5 pages vues :

GROUP BY author - mais les noms ne sont pas uniques

Cependant, nous avons en fait deux auteurs nommés Lisa dans notre tableau. Lorsque nous regroupons les résultats par la colonne auteur, les deux Lisa obtiennent la même moyenne. Pourquoi ? Parce que nous utilisons une colonne non unique sur le site GROUP BY. Cela signifie que toutes les valeurs de regroupement ne doivent pas être uniques. Si nous voulons voir la moyenne de chaque Lisa séparément, nous devons ajouter author_id (une colonne unique) à la liste GROUP BY:

SELECT 
  author, author_id
  avg(no_of_views) 
FROM recipes 
GROUP BY author, author_id;

Nous voyons maintenant que les recettes de Lisa(id=11) sont beaucoup plus consultées que les recettes de Lisa(id=5) :

GROUP BY avec auteur et author_id

Il est important de toujours penser aux clés de regroupement. Les valeurs de regroupement doivent être uniques et doivent représenter chaque groupe de la manière souhaitée. Sinon, vous obtiendrez des résultats imprécis et confus, voire une erreur GROUP BY.

5. Confusion entre COUNT(distinct) et COUNT(*)

Si vous êtes curieux de connaître le nombre total d'auteurs pour chaque catégorie de repas, vous pouvez écrire une instruction GROUP BY pour le calculer. Utilisons COUNT(*) et récupérons le nombre d'auteurs dans chaque catégorie :

SELECT 
  meal_category, 
  count(*) 
FROM recipes 
GROUP BY meal_category;

Voici le résultat - mais ce n'est pas ce que vous attendiez, n'est-ce pas ?

Il s'agit du nombre total de recettes dans chaque catégorie, et non du nombre total d'auteurs. Comment cela se fait-il ? Eh bien, COUNT(*) compte toutes les lignes de chaque groupe. La table recette contient des informations sur un recipe chaque enregistrement correspond à une recette. Cette requête compte les recettes (lignes) dans chaque catégorie, pas les auteurs des recettes.

Un auteur peut avoir plusieurs recettes dans chaque catégorie. Pour obtenir les informations souhaitées, vous devez donc compter les auteurs distincts (en utilisant COUNT(distinct author) au lieu de COUNT(*)) dans chaque groupe. Il s'agit d'une erreur très courante de GROUP BY.

Alors, quand devez-vous utiliser COUNT(*), COUNT(expression) et COUNT(distinct expression)?

Prenons un exemple :

SELECT 
  meal_category, 
  count(distinct author), 
  count(author),
  count(*) 
FROM recipes 
GROUP BY meal_category;

La différence entre COUNT(*) et COUNT(expression) est visible si nous effectuons des calculs sur une colonne qui comporte des valeurs manquantes. Lorsque des valeurs manquantes sont présentes, COUNT(*) comptera tous les enregistrements d'un groupe et COUNT(expression) ne comptera que les valeurs non nulles.

Dans l'exemple ci-dessus, COUNT(*) et COUNT(author) donnent exactement le même résultat car la colonne auteur ne comporte pas de valeurs NULL.

COUNT(distinct author) nous donne le nombre d'auteurs distincts pour chaque catégorie, ce qui n'est pas la même chose que COUNT(*). Par exemple, la catégorie repas apéritif froid contient six recettes de deux auteurs distincts. COUNT(*) compte le nombre de recettes (enregistrements) dans chaque catégorie, tandis que COUNT(distinct author) compte le nombre d'auteurs distincts.

Donc, si vous souhaitez afficher le nombre total d'auteurs distincts pour chaque catégorie de repas, utilisez COUNT(distinct author). Voici la requête correcte :

SELECT 
  meal_category, 
  count(distinct author)
FROM recipes 
GROUP BY meal_category;
GROUP BY meal_category;

Pour une explication plus détaillée, voir Quelle est la différence entre COUNT(*), COUNT(1), COUNT(nom de colonne), et COUNT(DISTINCT nom de colonne) ?

6. Problèmes d'utilisation des fonctions d'agrégation avec des valeurs NULL

Voici un autre problème de "valeur manquante". Disons que vous voulez calculer le nombre total moyen de vues du mois précédent pour chaque catégorie. Votre collègue a calculé ces chiffres, mais il aimerait que vous vérifiiez le résultat.

Voici votre requête :

SELECT
      meal_category,
      avg(no_of_views_lst_mth) as average,
  FROM recipes 
GROUP BY meal_category;

Et ce que vous obtenez est ...

Le résultat semble correct et vous êtes confiant quant à l'exactitude de votre requête. Cependant, votre collègue a obtenu des chiffres légèrement différents :

Que s'est-il passé ? Pourquoi ces résultats différents ?

En bref, les différents résultats proviennent d'interprétations différentes des valeurs manquantes.

La colonne no_of_views_lst_mth représente le nombre total de pages vues au cours du mois précédent. Si une recette a été créée pendant le mois en cours, cette colonne sera NULL pour cette ligne.

Par exemple, la recette du Gâteau au fromage à la banane d'Ally a été écrite pendant le mois en cours, il n'y a donc pas de statistiques pour le mois précédent :

Le gâteau au fromage à la banane a été publié dans le mois en cours.

Revenons maintenant à ces moyennes et à leurs différents résultats. Les moyennes sont calculées comme la somme totale de no_of_views_lst_mth divisée par le nombre total d'enregistrements. Si vous utilisez la fonction AVG() et que des NULL sont présents, le moteur ignore simplement les NULL et effectue les calculs sans eux. C'est ce qui s'est passé lorsque vous avez exécuté votre requête - les NULL ont été omis. Dans certains cas, vous voudrez remplacer les NULL par 0 (parce que la logique commerciale l'impose) ; c'est ce qu'a fait votre collègue, qui a produit des chiffres légèrement différents. Voici la requête de votre collègue :

SELECT
      meal_category,
           avg(CASE WHEN no_of_views_lst_mth is null 
            THEN 0 
            ELSE no_of_views_lst_mth END) AS average
FROM recipes 
GROUP BY meal_category;

Remarquez comment les moyennes de ces deux requêtes traitent les NULLs différemment. Par exemple, la catégorie "desserts" contient des NULLs. Ainsi, la première requête omet ces lignes et ne les compte pas dans le nombre total de lignes ; cela donne la valeur 144349.72. La deuxième requête remplace tous les NULLs par des zéros et compte ces lignes dans la moyenne, ce qui donne une valeur plus petite de 129914.75.

Je dirais que les deux requêtes pourraient être valables, selon la façon dont vous voulez calculer les moyennes.

7. Utilisation de COUNT(*) avec GROUP BY et LEFT JOIN

L'utilisation de GROUP BY avec une instruction LEFT JOIN peut être assez déroutante - surtout avec COUNT(). Voyons comment COUNT(*) et COUNT(expression) fonctionnent dans un LEFT JOIN.

Supposons qu'une personne du service marketing dispose du tableau suivant, recipes_campaigns. Il contient des informations sur le nombre de campagnes menées sur chaque catégorie de repas pendant le mois en cours :

recettes_campagne

En plus des données de recipes_campaignsle responsable marketing souhaite également connaître le nombre de recettes pour chaque catégorie de repas. Pour cela, nous aurons besoin des informations de la table recipes tableau. Nous allons donc joindre ces deux tableaux et calculer le nombre de recettes à l'aide de COUNT(*), comme suit :

SELECT 
      a.meal_category,
      count(*),
     FROM recipes_campaigns a 
LEFT JOIN recipes b ON a.meal_category=b.meal_category
GROUP BY a.meal_category;

Voici le résultat :

Ce n'est pas ce que nous attendions. Le tableau recipe ne contient aucune recette dans la catégorie "brunch", alors pourquoi avons-nous obtenu ce 1 dans le résultat ? Cela se produit parce que COUNT() est appliqué au résultat de LEFT JOIN! Lorsque vous consultez LEFT JOIN deux tableaux, la catégorie 'brunch' sera présente dans le résultat, même si aucune recette ou catégorie correspondante ne figure dans le tableau recipe table.

Comment pouvons-nous résoudre ce problème ? Si nous utilisons COUNT(expression) au lieu de COUNT(*), nous obtiendrons le résultat souhaité :

SELECT 
      a.meal_category,
      count(author_id),
FROM recipes_campaigns a 
LEFT JOIN recipes b ON a.meal_category=b.meal_category
GROUP BY a.meal_category;

Une fois que vous aurez exécuté ceci, vous obtiendrez :

Ici, COUNT(author_id) compte seulement les valeurs non NULL dans author_id après que LEFT JOIN soit exécuté. Il n'y a pas de valeur author_id pour la catégorie 'brunch' ; en d'autres termes, elle est NULL et le résultat pour cette catégorie est 0.

Vous pouvez résoudre les erreurs GROUP BY !

À travers plusieurs exemples, nous avons exploré GROUP BY et les erreurs les plus courantes que les débutants commettent souvent. J'espère que vous avez maintenant une meilleure idée du fonctionnement de GROUP BY et de ce qui cause ces erreurs bizarres ou ces résultats confus.

GROUP BY est vraiment très important dans la création de rapports. Si vous souhaitez apprendre à construire de bons rapports, je vous recommande notre Création de rapports basiques en SQL cours. Il comporte de nombreux exercices interactifs qui vous permettent d'acquérir de l'expérience dans la rédaction de requêtes GROUP BY pour les rapports. Et plus d'expérience réduit certainement la possibilité d'erreurs !