Comment résoudre l'erreur "must appear in the GROUP BY clause or be used in an aggregate function" dans PostgreSQL

Découvrez les causes de l'une des erreurs GROUP BY les plus courantes de PostgreSQL et comment vous pouvez la corriger !

En tant qu'utilisateur de PostgreSQL, vous rencontrez certainement de nombreux messages d'erreur lors de l'écriture d'un code SQL. Parfois, ils ne sont pas très clairs, mais vous n'aurez pas besoin d'un interprète pour celui-ci : "must appear in the GROUP BY clause or be used in an aggregate function" (doit apparaître dans la clause GROUP BY ou être utilisé dans une fonction d'agrégation).

Ce message d'erreur mentionne GROUP BY et les fonctions d'agrégation. Ce sont des concepts fondamentaux de PostgreSQL que vous devez maîtriser pour corriger cette erreur. Bien sûr, corriger cette erreur signifie également ne pas la reproduire trop souvent lorsque vous passez à des concepts avancés, tels que la création de rapports, l'utilisation de fonctions de fenêtre, d'ETC et de récursion, l'écriture de vos propres fonctions ou le travail avec des cartes. Tout cela est couvert dans notre parcours d'apprentissage Le SQL de A à Z in PostgreSQL. Ce cours interactif complet vous permettra d'éviter plus facilement les messages d'erreur de PostgreSQL. Et lorsqu'ils apparaissent, vous serez en mesure de les résoudre rapidement.

Comme vous le verrez bientôt, la résolution de l'erreur mentionnée dans le titre de l'article est également relativement facile. Elle nécessite cependant de comprendre le fonctionnement du GROUP BY dans PostgreSQL.

Qu'est-ce qui cause l'erreur "must appear in the GROUP BY clause or be used in an aggregate function" ?

Comme toujours, nous allons être très pratiques et utiliser du code SQL pour vous montrer ce qui cause l'erreur et comment la corriger.

Jeu de données

L'ensemble de données que nous allons utiliser se compose de deux tables. La première est freelancers:

• id - L'ID du free-lance et la clé primaire (PK) de la table.
• first_name - Le prénom du free-lance.
• last_name - Le nom de famille du free-lance.
• email - L'email du freelance.
• country - Le pays du freelance.

La requête CREATE TABLE est disponible ici.

Le deuxième tableau s'appelle weekly_salary et contient des données sur le montant de la rémunération hebdomadaire des free-lances. Les colonnes sont les suivantes :

• id - L'ID du salaire et la clé primaire (PK) de la table.
• freelancers_id - L'ID du free-lance et la clé étrangère (FK) de la table freelancers.
• week_start - La date de début du calcul du salaire.
• week_end - La date de fin du calcul du salaire.
• paid_amount - Le montant du salaire.

Voici la requête de création de la table.

Lancer l'erreur

Essayons d'afficher les noms et prénoms des free-lances ainsi que le nombre de salaires hebdomadaires qu'ils ont reçus jusqu'à présent :

SELECT
  first_name, 
  last_name,
  COUNT(freelancers_id) AS number_of_payments
FROM freelancers f
LEFT JOIN weekly_salary ws
ON f.id = ws.freelancers_id;

Nous LEFT JOIN les deux tables afin d'obtenir toutes les données nécessaires.

Ces données sont le nom et le prénom. Ensuite, nous utilisons la fonction d'agrégation COUNT() sur la colonne freelancers_id pour compter combien de fois cet ID apparaît. Le nombre compté est égal au nombre de salaires reçus par le free-lance.

Le nombre de salaires pour chaque freelance est donc... une erreur !

La formulation de ce message PostgreSQL est assez claire et reflète la règle générale : les colonnes énumérées dans SELECT doivent apparaître dans GROUP BY. Si elles n'apparaissent pas dans GROUP BY, elles doivent être utilisées dans la fonction d'agrégation.

Comme vous pouvez le constater, notre requête ne contient pas du tout GROUP BY. Nous utilisons la fonction d'agrégation COUNT() et nous devrions définir les groupes pour l'agrégation, mais nous ne l'avons pas fait.

Correction de l'erreur "must appear in the GROUP BY clause or be used in an aggregate function" (doit apparaître dans la clause GROUP BY ou être utilisé dans une fonction d'agrégation)

L'approche habituelle pour corriger cette erreur consiste à écrire toutes les colonnes de SELECT dans la clause GROUP BY. Cela exclut les colonnes qui sont le résultat de la fonction d'agrégation.

Dans notre cas, l'énumération des colonnes first_name et last_name dans GROUP BY corrigera l'erreur.

SELECT 
  first_name, 
  last_name,
  COUNT(freelancers_id) AS number_of_payments
FROM freelancers f
LEFT JOIN weekly_salary ws
ON f.id = ws.freelancers_id
GROUP BY first_name, last_name;

Le seul changement par rapport à la requête précédente est l'utilisation de GROUP BY. Nous y écrivons toutes les colonnes de SELECT à l'exception de celle utilisée dans la fonction d'agrégation. Les fonctions d'agrégation ne sont pas autorisées dans GROUP BY - un tout autre message d'erreur Postgres apparaîtrait.

La requête renvoie la sortie ci-dessus. Il est évident que nous avons vraiment corrigé l'erreur. Cette sortie montre que Miriam Valetti a été payée une fois, Claudio Stratos deux fois, et ainsi de suite.

Bonus : Optimiseur PostgreSQL, ou pourquoi cette erreur n'apparaît pas toujours

PostgreSQL utilise un optimiseur. Il essaie de "penser" et de faire des choses que vous vouliez mais que vous n'avez peut-être pas écrites explicitement.

L'erreur dont nous avons parlé n'apparaîtra pas dans PostgreSQL si vous groupez par la clé primaire.

Jetez un coup d'œil à cette requête :

SELECT 
  f.id,
  first_name, 
  last_name,
  SUM(paid_amount) AS total_paid_amount
FROM freelancers f
LEFT JOIN weekly_salary ws
ON f.id = ws.freelancers_id
WHERE country = 'Italy'
GROUP BY f.id;

Elle tente de renvoyer l'ID, le prénom, le nom de famille et le montant total du salaire versé jusqu'à présent aux free-lances (fonction SUM() ). Les deux tables sont LEFT JOINed et les données sont filtrées pour n'afficher que les free-lances d'Italie.

Ce qui semble étrange, c'est la fonction GROUP BY. Ne venons-nous pas de dire que toutes les colonnes de SELECT doivent également apparaître dans GROUP BY?

Ce code devrait renvoyer une erreur. Exécutons-le et voyons ce qu'il en est :

Étonnamment, la requête ne renvoie pas d'erreur ! Comment cela se fait-il ?

L'optimiseur de PostgreSQL vous permet de grouper par clé primaire (PK) et d'avoir les colonnes non-PK dans SELECT. PostgreSQL comprend que les colonnes PK déterminent les valeurs des autres colonnes de la même table.

Dans notre cas, la colonne f.id est la clé primaire. L'avoir dans GROUP BY est suffisant ; il n'est pas nécessaire d'inclure first_name et last_name (les colonnes non PK) dans GROUP BY.

Cependant, vous ne devez pas vous fier à cela : l'optimiseur n'est pas si intelligent que cela ! Par exemple, il ne peut pas identifier les PK pour les sous-requêtes, les CTE et les vues.

Réécrivons la requête ci-dessus avec une sous-requête. La table que vous utilisez peut être beaucoup plus grande, de sorte que vous pourriez vouloir filtrer uniquement les freelances d'Italie immédiatement. Vous ferez cela dans une sous-requête :

SELECT 
  ifr.id,
  first_name, 
  last_name,
  SUM(paid_amount) AS total_paid_amount
FROM (SELECT *
	FROM freelancers
	WHERE country = 'Italy') AS ifr 
LEFT JOIN weekly_salary ws
ON ifr.id = ws.freelancers_id
GROUP BY ifr.id;

La partie SELECT est la même que précédemment. Mais au lieu d'utiliser l'ensemble de la table freelancers et de la joindre avec weekly_salarynous écrivons la sous-requête ifr (comme dans 'italy_freelancers').

Cette sous-requête sélectionne toutes les colonnes de la table freelancers et filtre les données par pays à l'adresse WHERE.

Comme nous utilisons la sous-requête au lieu de la table entière, nous regroupons les résultats par pays. freelancers nous regroupons les résultats en fonction de la colonne id de la sous-requête.

Cela devrait fonctionner, car la sous-requête n'est en fait qu'une version limitée de la table freelancers table. Cependant, PostgreSQL renvoie une erreur familière :

Nous avons utilisé cette table dérivée comme une sous-requête, et l'optimiseur de PostgreSQL ne reconnaît donc pas son PK. Si vous insistez pour avoir une sous-requête, cette erreur est corrigée de la même manière que précédemment : listez toutes les autres colonnes de SELECT dans GROUP BY.

SELECT 
  ifr.id,
  first_name, 
  last_name,
  SUM(paid_amount) AS total_paid_amount
FROM (SELECT *
	FROM freelancers
	WHERE country = 'Italy') AS ifr 
LEFT JOIN weekly_salary ws
ON ifr.id = ws.freelancers_id
GROUP BY ifr.id, first_name, last_name;

La requête fonctionne maintenant :

Nous avons appris à réparer les erreurs. Il est maintenant temps de les éviter.

Obtenir de tels messages d'erreur dans PostgreSQL est très utile, car ils vous permettent d'apprendre. Bien sûr, même les utilisateurs les plus avancés de PostgreSQL verront de temps en temps cette erreur (ou n'importe quelle autre), donc vous ne les éviterez pas complètement.

Il est important que vous sachiez comment corriger les erreurs. Cependant, l'objectif est de voir cette erreur GROUP BY aussi rarement que possible. Bien sûr, l'optimiseur PostgreSQL peut parfois vous sauver. C'est un bon outil, mais vous devez compter davantage sur vos connaissances que sur la capacité de l'optimiseur à lire dans vos pensées. Pour s'en assurer, il faut beaucoup de pratique pour que l'écriture des colonnes dans SELECT puis dans GROUP BY devienne automatique.

Le site Le SQL de A à Z de PostgreSQL renforcera vos connaissances et votre confiance en lui.

Il existe également des idées supplémentaires sur la façon de pratiquer PostgreSQL en ligne. Utilisez ceci pour devenir votre propre optimiseur de code !