7 häufige GROUP BY-Fehler

Machen Sie diese Fehler mit GROUP BY in SQL? Finden Sie heraus, welche das sind, wie Sie sie vermeiden und wie Sie sie beheben können.

Die SQL-Anweisung GROUP BY kann schnell aussagekräftige Daten aufdecken. Auf den ersten Blick kann die Verwendung von GROUP BY einfach erscheinen - z. B. bei der Erstellung einfacher SQL-Berichte, die Sie Entscheidungsträgern in Ihrem Unternehmen vorlegen wollen. Doch beim Erlernen dieser leistungsstarken Funktion können Sie sich in seltsamen Fehlern verfangen oder falsche Ergebnisse erhalten, die durch unsachgemäß geschriebene GROUP BY Anweisungen verursacht werden. Wenn Sie das Gefühl haben, dass bei der Verwendung von GROUP BY nicht alles mit rechten Dingen zugeht, lesen Sie weiter. In diesem Artikel erkläre ich die häufigsten GROUP BY Fehler und wie Sie sie vermeiden können.

Vermeiden Sie diese 7 häufigen GROUP BY-Fehler

1. Vergessen von GROUP BY mit Aggregatfunktionen

Sie verwenden SELECT Anweisungen mit der GROUP BY Klausel, wenn Sie Zeilen in bestimmten Gruppen gruppieren und organisieren und dann eine bestimmte Berechnung für jede Gruppe durchführen möchten.

Der häufigste GROUP BY Fehler ist, dass Sie vergessen, GROUP BY in die SELECT-Anweisung zu schreiben.

Hier ist ein Beispiel. Stellen Sie sich vor, Sie haben die Tabelle Rezepte, die 100 Datensätze und sechs Spalten enthält. Diese Tabelle speichert die Anzahl der Aufrufe (no_of_views) für jedes auf einer bekannten kulinarischen Website veröffentlichte Rezept:

Rezepttabelle

Hier ist eine kurze Beschreibung der Spalten der Tabelle:

• meal_category - Die Kategorie des Rezepts (Suppe, Getränke, Desserts, usw.).
• name - Der Name des Rezepts.
• author - Der Name des Autors.
• no_of_views - Die Anzahl der Aufrufe (Gesamtseiten/Rezepte) im aktuellen Monat.
• no_of_views_lst_mth - Die Anzahl der Aufrufe (insgesamt angesehene Seiten/Rezepte) im Vormonat.
• author_id - Die eindeutige ID-Nummer des Autors.

Nehmen wir an, Sie möchten die Anzahl der Rezepte in jeder Mahlzeitenkategorie zählen. Wenn Sie die Anweisung wie folgt schreiben (ohne GROUP BY am Ende) ...

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

... geben die meisten SQL-Programme einen Fehler aus. Einige Engines geben jedoch seltsame, unerwünschte Ergebnisse aus. Ich verwende MySQL, und wenn ich diese Anweisung ausführe, erhalte ich das folgende Ergebnis:

Ergebnis ohne GROUP BY

100 ist die Gesamtzahl aller Rezepte im gesamten Datensatz, und die Mahlzeitenkategorie "Kalte Vorspeise" ist nur eine von zehn Kategorien. Um diese Art von Fehler zu korrigieren, müssen Sie ein GROUP BY meal_category am Ende der Anweisung hinzufügen. (Andernfalls ergibt Ihr Ergebnis in MySQL einfach keinen Sinn.)

Das korrekte SELECT sieht wie folgt aus:

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

Hier ist eine kurze Erklärung, was vor sich geht:

• Die Datensätze werden auf der Grundlage der Kategorie der Mahlzeit zusammengeführt. Zum Beispiel sind Desserts eine Gruppe, Suppen eine andere, Hauptgerichte eine andere usw. Die Spalte meal_category wird nach GROUP BY angegeben; sie ist auch in SELECT aufgeführt.
• Für jede Gruppe verwenden wir COUNT(*), um die Gesamtzahl der Rezepte in dieser Gruppe zu zählen.

Ich werde hier nicht tief in die Syntax eintauchen, aber ich würde Ihnen auf jeden Fall empfehlen, GROUP BY in SQL Explained oder Using GROUP BY in SQL zu lesen, um mehr Details zu erfahren.

Wie Sie sehen können, ist das Ergebnis wie erwartet:

Gültiges GROUP BY-Ergebnis

2. WHERE und HAVING verwechseln

Vielleicht möchten Sie nur die Mahlzeitenkategorien sehen, die mehr als 10 Rezepte haben. Viele Anfänger würden diese Abfrage schreiben:

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

Diese Anweisung gibt einen Fehler zurück, weil Sie keine Aggregatfunktionen in einer WHERE Klausel verwenden können. WHERE wird mit GROUP BY verwendet, wenn Sie Zeilen filtern möchten , bevor Sie sie gruppieren.

In unserem Beispiel wollen wir Zeilen nach der Gruppierung filtern; in solchen Fällen müssen wir die HAVING Klausel verwenden:

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

Dieses Missverständnis über den Unterschied zwischen HAVING und WHERE ist der zweithäufigste Fehler bei GROUP BY.

Lassen Sie uns diesen Unterschied anhand von zwei weiteren Beispielen verdeutlichen.

Beispiel 1 - Anzeige von Mahlzeitenkategorien mit 1M+ Views

Eine Anweisung, die nur Kategorien mit mehr als 1 Million Seitenzugriffen anzeigt, kann wie folgt geschrieben werden:

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

Hier verwenden wir HAVING, weil wir die Datensätze filtern wollen, nachdem sie gruppiert wurden. Das Ergebnis ist unten dargestellt:

Beispiel mit HAVING

Beispiel 2 - Johns Leistung in jeder Mahlzeitenkategorie

Diese Abfrage extrahiert nur Johns Rezepte und berechnet seine Leistung:

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

Wir verwenden WHERE, weil wir die Datensätze filtern müssen (damit wir nur die Daten von John erhalten), bevor wir die Datensätze in Gruppen nach Mahlzeitenkategorie einordnen. So sieht das Ergebnis aus:

Johns KPIs

HAVING und WHERE sind in unseren Artikeln Was ist der Unterschied zwischen WHERE- und HAVING-Klauseln in SQL? und 5 Beispiele für GROUP BY gut beschrieben. Wenn Sie mehr Beispiele zu diesem Thema sehen möchten, empfehle ich Ihnen, dort zu beginnen.

3. Auflistung einer Spalte in SELECT, aber nicht in GROUP BY

Nehmen wir nun an, Sie möchten die Gesamtzahl der Aufrufe pro meal_category und author sehen. Wir können das tun - wir müssen nur die Autorenspalte zu unserer vorherigen Abfrage hinzufügen:

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

Sieht das für Sie in Ordnung aus? Natürlich nicht; die meisten SQL-Engines werden einen Fehler ausgeben. Oracle zum Beispiel wird Ihnen sagen ".: Kein GROUP BY-Ausdruck". Warum dieser verwirrende Fehler? Was fehlt hier?

Nun, die SQL-Engine weiß nicht, wie sie die Gesamtzahl für jeden Autor berechnen soll, weil wir sie nicht in die GROUP BY-Klausel aufgenommen haben; das Attribut author ist nicht in der GROUP BY-Klausel aufgeführt. Dies ist ein weiterer häufiger Fehler bei GROUP BY.

Wir korrigieren diese Abfrage und führen sie noch einmal aus:

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

Das Ergebnis ist:

Jetzt sieht es gut aus. Denken Sie daran, dass unaggregierte Spalten, die in SELECT aufgeführt sind, auch in aufgeführt sein müssen.GROUP BYIn unserem Fall sind die nicht aggregierten Spalten meal_category und author, die jetzt in SELECT und GROUP BY stehen.

Spalten, die in Aggregatfunktionen enthalten sind, werden in GROUP BY nicht aufgeführt. In unserem Beispiel wird die Spalte no_of_views in der Aggregatfunktion SUM() verwendet und ist daher nicht in der Klausel GROUP BY aufgeführt.

Wenn Sie mehr über dieses Thema erfahren möchten, lesen Sie unseren Artikel GROUP BY-Klausel: How Well Do You Know It? Darin wird erklärt, warum SELECTed-Spalten in der GROUP BY-Klausel erscheinen müssen. Außerdem finden Sie in How to Fix a 'Not a GROUP BY Expression' Error weitere Beispiele für diese Art von Fehler.

4. Keine Gruppierung nach einem eindeutigen Schlüssel

Lassen Sie uns nun etwas anderes versuchen. Angenommen, wir möchten die durchschnittliche Anzahl der Seitenaufrufe für jeden Rezeptautor ermitteln. Die folgende Abfrage berechnet die durchschnittliche Gesamtzahl der Seitenaufrufe für jeden Autor anhand des Autorennamens:

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

Wenn Sie sich das Ergebnis ansehen, werden Sie feststellen, dass Lisa im Durchschnitt 116101,5 Seitenaufrufe hat:

GROUP BY Autor - aber die Namen sind nicht eindeutig

Wir haben jedoch zwei Autoren namens Lisa in unserer Tabelle. Wenn wir die Ergebnisse nach der Autorenspalte gruppieren, werden beide Lisas zusammen gemittelt. Und warum? Weil wir eine nicht eindeutige Spalte in der GROUP BY verwenden. Das bedeutet, dass nicht alle Gruppierungswerte eindeutig sein müssen. Wenn wir den Durchschnitt jeder Lisa separat sehen wollen, sollten wir author_id (eine eindeutige Spalte) zur GROUP BY Liste hinzufügen:

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

Jetzt sehen wir, dass Rezepte von Lisa(id=11) viel häufiger aufgerufen werden als Rezepte von Lisa(id=5):

GROUP BY mit Autor und Autor_id

Es ist wichtig, immer über Gruppierungsschlüssel nachzudenken. Die Gruppierungswerte sollten eindeutig sein und müssen jede Gruppe auf die gewünschte Weise repräsentieren. Andernfalls erhalten Sie ungenaue, verwirrende Ergebnisse und möglicherweise einen GROUP BY-Fehler.

5. Verwechslung von COUNT(distinct) und COUNT(*)

Wenn Sie die Gesamtzahl der Autoren für jede Mahlzeitenkategorie sehen möchten, können Sie eine GROUP BY -Anweisung schreiben, um diese zu berechnen. Verwenden wir COUNT(*) und ermitteln wir die Anzahl der Autoren in jeder Kategorie:

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

Hier ist das Ergebnis - aber es ist nicht das, was Sie erwartet haben, stimmt's?

Es handelt sich um die Gesamtzahl der Rezepte in jeder Kategorie, nicht um die Gesamtzahl der Autoren. Warum ist das so? Nun, COUNT(*) zählt alle Zeilen in jeder Gruppe. Die Tabelle Rezept enthält Informationen auf einer recipe Ebene - jeder Datensatz ist ein Rezept. Diese Abfrage zählt die Rezepte (Zeilen) in jeder Kategorie, nicht die Rezeptautoren.

Ein Autor kann viele Rezepte in jeder Kategorie haben. Um die gewünschten Informationen zu erhalten, müssen Sie also die einzelnen Autoren innerhalb jeder Gruppe zählen (mit COUNT(distinct author) statt COUNT(*)). Dies ist ein sehr häufiger GROUP BY Fehler.

Wann sollten Sie also COUNT(*), COUNT(expression) und COUNT(distinct expression) verwenden?

Schauen wir uns ein Beispiel an:

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

Der Unterschied zwischen COUNT(*) und COUNT(expression) wird sichtbar, wenn wir Berechnungen mit einer Spalte durchführen, die einige fehlende Werte enthält. Wenn fehlende Werte vorhanden sind, zählt COUNT(*) alle Datensätze in einer Gruppe, während COUNT(expression) nur Werte zählt, die nicht null sind.

Im obigen Beispiel liefern COUNT(*) und COUNT(author) genau das gleiche Ergebnis, da die Spalte Autor keine NULL-Werte enthält.

COUNT(distinct author) gibt uns die Anzahl der unterschiedlichen Autoren für jede Kategorie, was nicht dasselbe ist wie COUNT(*). Die Kategorie "Kalte Vorspeise" enthält zum Beispiel sechs Rezepte von zwei verschiedenen Autoren. COUNT(*) zählt die Anzahl der Rezepte (Datensätze) in jeder Kategorie, während COUNT(distinct author) die Anzahl der verschiedenen Autoren zählt.

Wenn Sie also die Gesamtzahl der unterschiedlichen Autoren pro Kategorie anzeigen möchten, verwenden Sie COUNT(distinct author). Hier ist die richtige Abfrage:

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

Eine ausführlichere Erklärung finden Sie unter Was ist der Unterschied zwischen COUNT(*), COUNT(1), COUNT(Spaltenname) und COUNT(DISTINCT Spaltenname)?

6. Probleme bei der Verwendung von Aggregatfunktionen mit NULLs

Dies ist ein weiteres Problem mit "fehlenden Werten". Angenommen, Sie möchten die durchschnittliche Gesamtzahl der Aufrufe des Vormonats für jede Kategorie berechnen. Ihr Kollege hat diese Zahlen berechnet, aber er möchte, dass Sie das Ergebnis noch einmal überprüfen.

Hier ist Ihre Abfrage:

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

Und was Sie erhalten, ist ...

Das Ergebnis sieht gut aus, und Sie sind sich sicher, dass Ihre Abfrage richtig ist. Ihr Kollege hat jedoch etwas andere Zahlen erhalten:

Was ist da gerade passiert? Warum die unterschiedlichen Ergebnisse?

Kurz gesagt, die unterschiedlichen Ergebnisse ergeben sich aus unterschiedlichen Interpretationen fehlender Werte.

Die Spalte no_of_views_lst_mth steht für die Anzahl der gesamten Seitenaufrufe im Vormonat. Wenn ein Rezept im aktuellen Monat erstellt wurde, ist diese Spalte für diese Zeile NULL.

Das Rezept "Banana Cheesecake" von Ally wurde beispielsweise im aktuellen Monat erstellt, daher gibt es keine Statistik für den Vormonat:

Bananenkäsekuchen wurde im aktuellen Monat veröffentlicht

Kommen wir nun zurück zu den Durchschnittswerten und ihren unterschiedlichen Ergebnissen. Durchschnittswerte werden berechnet als die Gesamtsumme von no_of_views_lst_mth geteilt durch die Gesamtzahl der Datensätze. Wenn Sie die Funktion AVG() verwenden und NULLs vorhanden sind, ignoriert die Engine die NULLs einfach und führt die Berechnungen ohne sie durch. Genau das ist passiert, als Sie Ihre Abfrage ausgeführt haben - die NULLs wurden ausgelassen. In manchen Fällen sollten Sie NULLs durch 0 ersetzen (weil die Geschäftslogik dies vorschreibt); dies hat Ihr Kollege getan, was zu leicht abweichenden Zahlen führte. Hier ist die Abfrage Ihres Kollegen:

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;

Beachten Sie, dass die Durchschnittswerte dieser beiden Abfragen NULLs unterschiedlich behandeln. Zum Beispiel enthält die Kategorie "Desserts" NULLs. Daher lässt die erste Abfrage diese Zeilen aus und zählt sie nicht zur Gesamtzahl der Zeilen; dies ergibt den Wert 144349,72. Die zweite Abfrage ersetzt alle NULLs durch Null und zählt diese Zeilen im Durchschnitt, was einen kleineren Wert von 129914,75 ergibt.

Ich würde sagen, dass beide Abfragen gültig sein können, je nachdem, wie Sie die Durchschnittswerte berechnen wollen.

7. Verwendung von COUNT(*) mit GROUP BY und einem LEFT JOIN

Die Verwendung von GROUP BY mit einer LEFT JOIN -Anweisung kann ziemlich verwirrend sein - insbesondere mit COUNT(). Schauen wir uns an, wie COUNT(*) und COUNT(expression) in einer LEFT JOIN funktionieren.

Nehmen wir an, dass jemand im Marketing die folgende Tabelle hat, recipes_campaigns. Sie enthält Informationen über die Anzahl der Kampagnen, die im aktuellen Monat für jede Mahlzeitenkategorie durchgeführt wurden:

Rezepte_Kampagne

Zusätzlich zu den Daten in recipes_campaignsmöchte der Vermarkter auch die Anzahl der Rezepte für jede Mahlzeitenkategorie sehen. Dafür benötigen wir die Informationen aus der recipes Tabelle. Verknüpfen wir also diese beiden Tabellen und berechnen die Anzahl der Rezepte mit Hilfe von COUNT(*), etwa so:

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;

Hier ist das Ergebnis:

Das ist nicht das, was wir erwartet haben. Die Tabelle recipe enthält keine Rezepte in der Kategorie "Brunch". Warum haben wir dann diese 1 im Ergebnis? Das passiert, weil COUNT() auf das Ergebnis von LEFT JOIN angewendet wird! Wenn Sie LEFT JOIN auf zwei Tabellen anwenden, wird die Kategorie "Brunch" in der Ausgabe vorhanden sein - auch wenn es keine passenden Rezepte oder Kategorien in der recipe Tabelle gibt.

Wie können wir das Problem lösen? Wenn wir COUNT(expression) anstelle von COUNT(*) verwenden, erhalten wir das gewünschte Ergebnis:

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;

Sobald Sie dies ausführen, erhalten Sie:

Hier zählt COUNT(author_id) nur die Nicht-NULL-Werte in author_id , nachdem LEFT JOIN ausgeführt wurde. Es gibt keinen author_id Wert für die Kategorie "Brunch"; mit anderen Worten, er ist NULL und das Ergebnis für diese Kategorie ist 0.

Sie können GROUP BY-Fehler beheben!

Anhand mehrerer Beispiele haben wir uns mit GROUP BY und den häufigsten Fehlern beschäftigt, die Anfängern häufig unterlaufen. Ich hoffe, dass Sie jetzt ein besseres Verständnis dafür haben, wie GROUP BY funktioniert und was die Ursache für diese seltsamen Fehler oder verwirrenden Ergebnisse ist.

GROUP BY ist bei der Erstellung von Berichten wirklich sehr wichtig. Wenn Sie lernen wollen, wie man gute Berichte erstellt, empfehle ich Ihnen unseren Erstellen einfacher SQL-Berichte Kurs. Er enthält viele interaktive Übungen, mit denen Sie Erfahrungen beim Schreiben von GROUP BY Abfragen für Berichte sammeln können. Und mehr Erfahrung verringert die Wahrscheinlichkeit von Fehlern!