Wie man den Fehler "must appear in the GROUP BY clause or be used in an aggregate function" in PostgreSQL behebt

Erfahren Sie, was einen der häufigsten PostgreSQL GROUP BY-Fehler verursacht und wie Sie ihn beheben können!

Als PostgreSQL-Benutzer stoßen Sie sicherlich auf viele Fehlermeldungen, wenn Sie einen SQL-Code schreiben. Manchmal sind sie ziemlich unklar, aber für diese Fehlermeldung brauchen Sie keinen Interpreter: "must appear in the GROUP BY clause or be used in an aggregate function".

In dieser Fehlermeldung werden GROUP BY und Aggregatfunktionen erwähnt. Dies sind grundlegende PostgreSQL-Konzepte, die Sie beherrschen müssen, um diesen Fehler zu beheben. Natürlich bedeutet die Behebung dieses Fehlers auch, dass Sie diesen Fehler nicht zu oft machen, wenn Sie zu fortgeschrittenen Konzepten übergehen, wie z.B. das Erstellen von Berichten, die Verwendung von Fensterfunktionen, CTEs und Rekursion, das Schreiben eigener Funktionen oder das Arbeiten mit Maps. All dies wird in unserem SQL von A bis Z in PostgreSQL Lernpfad behandelt. Dieser umfassende interaktive Kurs wird es Ihnen erleichtern, PostgreSQL-Fehlermeldungen zu vermeiden. Und wenn sie auftreten, werden Sie in der Lage sein, sie schnell zu lösen.

Wie Sie bald sehen werden, ist auch die Lösung des im Titel des Artikels genannten Fehlers relativ einfach. Sie erfordert jedoch ein Verständnis dafür, wie GROUP BY in PostgreSQL funktioniert.

Was ist die Ursache für den Fehler "must appear in the GROUP BY clause or be used in an aggregate function"?

Wie immer werden wir sehr praktisch sein und SQL-Code verwenden, um Ihnen zu zeigen, was den Fehler verursacht und wie man ihn behebt.

Datensatz

Der Datensatz, den wir verwenden werden, besteht aus zwei Tabellen. Die erste ist freelancers:

• id - Die ID des Freelancers und der Primärschlüssel (PK) der Tabelle.
• first_name - Der Vorname des Freelancers.
• last_name - Der Nachname des Freiberuflers.
• email - Die E-Mail des Freelancers.
• country - Das Land des Freiberuflers.

Die Abfrage CREATE TABLE ist hier verfügbar.

Die zweite Tabelle heißt weekly_salary und enthält Daten darüber, wie viel Freiberufler pro Woche bezahlt werden. Die Spalten sind:

• id - Die ID des Gehalts und der Primärschlüssel (PK) der Tabelle.
• freelancers_id - Die ID des Freelancers und der Fremdschlüssel der Tabelle (FK) aus der Tabelle Freelancer.
• week_start - Das Anfangsdatum für die Gehaltsberechnung.
• week_end - Das Enddatum für die Gehaltsberechnung.
• paid_amount - Die Höhe des Gehalts.

Hier ist die Abfrage zum Erstellen der Tabelle.

Auslösen des Fehlers

Versuchen wir nun, die Vor- und Nachnamen der Freiberufler und die Anzahl der Wochengehälter, die sie bisher erhalten haben, anzuzeigen:

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;

Wir LEFT JOIN die beiden Tabellen, so dass wir alle erforderlichen Daten erhalten können.

Diese Daten sind der Vor- und der Nachname. Dann verwenden wir die Aggregatfunktion COUNT() für die Spalte freelancers_id, um zu zählen, wie oft diese ID vorkommt. Die gezählte Anzahl ist gleich der Anzahl der Gehälter, die der Freiberufler erhalten hat.

Die Anzahl der Gehälter für jeden Freiberufler ist also...ein Fehler!

Der Wortlaut dieser PostgreSQL-Meldung ist recht eindeutig und spiegelt die allgemeine Regel wider: Die in SELECT aufgeführten Spalten sollten in GROUP BY erscheinen. Wenn sie nicht in GROUP BY erscheinen, müssen sie in der Aggregatfunktion verwendet werden.

Wie Sie sehen können, enthält unsere Abfrage überhaupt keine GROUP BY. Wir verwenden die Aggregatfunktion COUNT() und sollten die Gruppen für die Aggregation definieren, aber das haben wir nicht getan.

Behebung des Fehlers "must appear in the GROUP BY clause or be used in an aggregate function".

Der übliche Ansatz zur Behebung dieses Fehlers besteht darin, einfach alle Spalten von SELECT in die GROUP BY Klausel zu schreiben. Dies schließt die Spalten aus, die das Ergebnis der Aggregatfunktion sind.

In unserem Fall behebt die Auflistung der Spalten first_name und last_name in GROUP BY den Fehler.

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;

Die einzige Änderung gegenüber der vorherigen Abfrage ist, dass wir jetzt GROUP BY verwenden. Darin schreiben wir alle Spalten von SELECT mit Ausnahme derjenigen, die in der Aggregatfunktion verwendet wird. Aggregatfunktionen sind in GROUP BY nicht erlaubt - dies würde eine ganz andere Postgres-Fehlermeldung anzeigen.

Die Abfrage gibt die obige Ausgabe zurück. Es ist offensichtlich, dass wir den Fehler wirklich behoben haben. Diese Ausgabe zeigt, dass Miriam Valetti einmal bezahlt wurde, Claudio Stratos zweimal, und so weiter.

Bonus: PostgreSQL Optimizer, oder warum dieser Fehler nicht immer auftritt

PostgreSQL verwendet einen Optimierer. Er versucht zu "denken" und Dinge zu tun, die Sie zwar gemeint, aber vielleicht nicht explizit geschrieben haben.

Der Fehler, den wir besprochen haben, tritt in PostgreSQL nicht auf, wenn Sie nach dem Primärschlüssel gruppieren.

Werfen Sie einen Blick auf diese Abfrage:

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;

Sie versucht, die ID, den Vornamen, den Nachnamen und den Gesamtbetrag der bisher gezahlten Gehälter der Freiberufler zurückzugeben (die Funktion SUM() ). Die beiden Tabellen sind LEFT JOINed und die Daten sind so gefiltert, dass nur Freiberufler aus Italien angezeigt werden.

Was seltsam erscheint, ist die GROUP BY. Hatten wir nicht gerade gesagt, dass alle Spalten in SELECT auch in GROUP BY erscheinen müssen?

Dieser Code sollte einen Fehler zurückgeben. Lassen wir ihn laufen und sehen wir nach:

Erstaunlicherweise gibt die Abfrage keinen Fehler zurück! Woran liegt das?

Der PostgreSQL-Optimierer erlaubt es, nach Primärschlüssel (PK) zu gruppieren und die Nicht-PK-Spalten in SELECT zu haben. PostgreSQL versteht, dass die PK-Spalten die Werte der anderen Spalten in derselben Tabelle bestimmen.

In unserem Fall ist die Spalte f.id der PK. Es reicht aus, sie in GROUP BY zu haben; first_name und last_name (die Nicht-PK-Spalten) müssen nicht in GROUP BY aufgenommen werden.

Sie sollten sich jedoch nicht darauf verlassen - der Optimierer ist nicht so schlau! Er kann zum Beispiel keine PKs für Unterabfragen, CTEs und Ansichten erkennen.

Lassen Sie uns die obige Abfrage mit einer Unterabfrage umschreiben. Die Tabelle, die Sie verwenden, könnte viel größer sein, so dass Sie vielleicht nur die Freiberufler aus Italien sofort filtern möchten. Dies tun Sie in einer Unterabfrage:

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;

Der SELECT Teil ist derselbe wie zuvor. Aber anstatt die gesamte freelancers Tabelle zu verwenden und sie mit weekly_salaryzu verbinden, schreiben wir die ifr (wie in 'italy_freelancers') als Unterabfrage.

Diese Unterabfrage wählt alle Spalten aus der Tabelle freelancers und filtert die Daten nach Land in WHERE.

Da wir die Unterabfrage anstelle der gesamten freelancers Tabelle verwenden, gruppieren wir die Ausgabe nach der Spalte id aus der Unterabfrage.

Dies sollte funktionieren, da die Unterabfrage im Grunde nur eine eingeschränkte Version der freelancers Tabelle ist. PostgreSQL gibt jedoch einen bekannten Fehler zurück:

Wir haben diese abgeleitete Tabelle als Subquery verwendet, so dass der PostgreSQL-Optimierer ihre PK nicht erkennt. Wenn Sie darauf bestehen, eine Subquery zu haben, dann wird dieser Fehler auf die gleiche Weise behoben wie zuvor: Listen Sie alle anderen Spalten von SELECT in GROUP BY auf.

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;

Die Abfrage funktioniert jetzt:

Wir haben das Beheben von Fehlern gelernt. Jetzt ist es an der Zeit, sie zu vermeiden.

Solche Fehlermeldungen in PostgreSQL zu erhalten, ist sehr hilfreich, da man dadurch lernt. Natürlich werden auch die fortgeschrittensten PostgreSQL-Benutzer hin und wieder auf diesen (oder einen anderen) Fehler stoßen, so dass man sie nicht ganz vermeiden kann.

Es ist wichtig, dass Sie wissen, wie Sie die Fehler beheben können. Das Ziel ist jedoch, diesen GROUP BY-Fehler so selten wie möglich zu sehen. Natürlich kann der PostgreSQL-Optimierer Sie manchmal retten. Er ist ein gutes Werkzeug, aber Sie sollten sich mehr auf Ihr Wissen verlassen als darauf, dass der Optimierer Ihre Gedanken lesen kann. Um das zu gewährleisten, brauchen Sie viel Übung, um die Spalten in SELECT und dann in GROUP BY automatisch zu schreiben.

Die SQL von A bis Z in PostgreSQL wird Ihre Kenntnisse und Ihr Vertrauen in PostgreSQL stärken.

Es gibt auch einige zusätzliche Ideen, wie man PostgreSQL online üben kann. Nutzen Sie dies, um Ihr eigener Code-Optimierer zu werden!