Gruppierungen

(Einführung in SQL: Gruppierungen)

(Einführung in SQL: Gruppierungen)

Abfragen werden sehr häufig gruppiert, weil nicht nur einzelne Informationen, sondern auch Zusammenfassungen gewünscht werden. Durch die GROUP BY-Klausel im SELECT-Befehl werden alle Zeilen, die in einer oder mehreren Spalten den gleichen Wert enthalten, in jeweils einer Gruppe zusammengefasst.

Dies ist in der Regel nur dann sinnvoll, wenn in der Spaltenliste des SELECT-Befehls eine gruppenweise Auswertung, also eine der Spaltenfunktionen enthalten ist.

Syntax von GROUP BY

Die GROUP BY-Klausel hat folgenden allgemeinen Aufbau:

 GROUP BY <Spaltenliste>

Die Spaltenliste enthält, durch Komma getrennt, die Namen von einer oder mehreren Spalten. Für jede Spalte kann eine eigene Sortierung angegeben werden:

   <Spaltenname>
   -- oder
   <Spaltenname> COLLATE <Collation-Name>

Die Spalten in der Spaltenliste können meistens wahlweise mit dem Spaltennamen der Tabelle, mit dem Alias-Namen aus der Select-Liste oder mit Spaltennummer gemäß der Select-Liste (ab 1 gezählt) angegeben werden.

In der Regel enthält die Abfrage eine der Aggregatfunktionen und wird durch ORDER BY nach den gleichen Spalten sortiert.

Gruppierung bei einer Tabelle

Im einfachsten Fall werden Daten nach einer Spalte gruppiert und gezählt.

Aufgabe

Im folgenden Beispiel wird die Anzahl der Abteilungen für jeden Ort aufgeführt.

SELECT Ort,
       COUNT(*) AS Anzahl
  FROM Abteilung
 GROUP BY Ort
 ORDER BY Ort;

Ausgabe

ORT     ANZAHL
---     ------
Bochum     3
Dortmund   4
Essen      4
Herne      1

Aufgabe

Die folgende Abfrage listet auf, wie viele Mitarbeiter es in den Abteilungen und Raumnummern gibt:

SELECT Abteilung_ID AS Abt,
       Raum,
       COUNT(*) AS Anzahl
  FROM Mitarbeiter
 GROUP BY Abt, Raum
 ORDER BY Abt, Raum;

Ausgabe

ABT  RAUM  ANZAHL
---  ----  ------
 1   112      1
 1   113      1
 2   212      2
 3   312      1
 3   316      1
 4   412      2   // usw.

Am folgenden Beispiel wird die Gruppierung besonders deutlich.

Aufgabe

Berechne die mittlere Schadenshöhe für die Schadensfälle mit und ohne Personenschäden.

SELECT Verletzte, 
       AVG(Schadenshoehe) AS Mittlere_Schadenshoehe 
  FROM Schadensfall
 GROUP BY Verletzte;

Ausgabe

VERLETZTE  MITTLERE_SCHADENSHOEHE
    J          3.903,87
    N          1.517,45

Die Spalte Verletzte enthält entweder 'J' oder 'N' und ist verpflichtend, kann also keine NULL-Werte enthalten. Deshalb werden durch die GROUP BY-Anweisung eine oder zwei Gruppen gebildet. Für jede Gruppe wird der Mittelwert gesondert berechnet aus den Werten, die in der Gruppe vorkommen. In diesem Fall liefert die Funktion AVG also ein oder zwei Ergebnisse, abhängig davon, welche Werte in der Spalte Verletzte überhaupt vorkommen.

Zeilen, bei denen einer der Werte zur Gruppierung fehlt, oder Zeilen mit NULL-Werten werden als eigene Gruppe gezählt.

Gruppierung über mehrere Tabellen

Eine Gruppierung kann auch Felder aus verschiedenen Tabellen auswerten. Dafür sind zunächst die Voraussetzungen für die Verknüpfung mehrerer Tabellen zu beachten. Bitte beachten Sie das folgende Beispiel.

Aufgabe

Gesucht wird für jeden Fahrzeughersteller (mit Angabe von ID und Name) und Jahr die Summe der Schadenshöhe aus der Tabelle Schadensfall.

SELECT fh.ID AS Hersteller_ID,
       fh.Name AS Hersteller_Name,
       EXTRACT(YEAR FROM sf.Datum) AS Jahr,
       SUM(sf.Schadenshoehe) AS Schadenssumme
  FROM Schadensfall   sf
       JOIN Zuordnung_SF_FZ    zu ON sf.ID = zu.Schadensfall_ID
       JOIN Fahrzeug           fz ON fz.ID = zu.Fahrzeug_ID
       JOIN Fahrzeugtyp        ft ON ft.ID = fz.Fahrzeugtyp_ID
       JOIN Fahrzeughersteller fh ON fh.ID = ft.Hersteller_ID
 GROUP BY Hersteller_ID, Hersteller_Name, Jahr
 ORDER BY Jahr, Hersteller_ID;

Ausgabe

HERSTELLER_ID  HERSTELLER_NAME  JAHR  SCHADENSSUMME
-------------  ---------------  ----  -------------
       9       Volvo            2007       2.066,00
      10       Renault          2007       5.781,60
      11       Seat             2007       1.234,50
       2       Opel             2008       1.438,75
      11       Seat             2008       1.983,00
       9       Volvo            2009       4.092,15
      10       Renault          2009         865,00

Ausgangspunkt ist die Tabelle Schadensfall, weil aus deren Einträgen die Summe gebildet werden soll. Durch JOIN werden nacheinander die verknüpften Tabellen herangezogen, und zwar jeweils durch die ID auf die Verknüpfung: Schadensfall → Zuordnung → Fahrzeug → Fahrzeugtyp → Hersteller. Dann stehen ID und Name aus der Tabelle Fahrzeughersteller zur Verfügung, die für die Gruppierung gewünscht werden.

Zur Gruppierung genügt eigentlich die Verwendung von Hersteller_ID. Aber man möchte sich natürlich den Herstellernamen anzeigen lassen. Allerdings gibt es einen Fehler, wenn man den Namen nur in der SELECT-Liste benutzt und in der GROUP BY-Liste streicht:

 Fehler

SELECT fh.ID AS Hersteller_ID,
       fh.Name AS Hersteller_Name,
       EXTRACT(YEAR FROM sf.Datum) AS Jahr,
       SUM(sf.Schadenshoehe) AS Schadenssumme
  FROM Schadensfall   sf
       join ... (wie oben)
 group by Hersteller_ID, Jahr  /* oder Variante 2: */
 group by Hersteller_ID, EXTRACT(YEAR FROM sf.Datum)
 order by Jahr, Hersteller_ID

Ausgabe

Ungültiger Ausdruck in der Select-Liste (fehlt entweder in einer Aggregatfunktion oder in der GROUP BY-Klausel).

Siehe dazu die Erläuterungen unter Einschränkungen.

Einschränkungen

Wie das letzte Beispiel zeigt, muss die GROUP BY-Klausel gewisse Bedingungen erfüllen. Auch dafür gilt: Jedes DBMS weicht an manchen Punkten vom Standard ab.

• Jeder Spaltenname der SELECT-Auswahl, der nicht zu einer Aggregatfunktion gehört, muss auch in der GROUP BY-Klausel benutzt werden.

Diese Bedingung wird im letzten Beispiel verletzt: Hersteller_Name steht in der SELECT-Liste, aber nicht in der GROUP BY-Klausel. In diesem Fall ist eine Änderung einfach, weil ID und Name des Herstellers gleichwertig sind. Übrigens erlaubt MySQL auch die Auswahl von Feldern, die in der GROUP BY-Klausel nicht genannt sind.

Umgekehrt ist es in der Regel möglich, eine Spalte per GROUP BY zu gruppieren, ohne die Spalte selbst in der SELECT-Liste zu verwenden.

• GROUP BY kann Spalten der Tabellen, abgeleiteter Tabellen oder VIEWs in der FROM-Klausel oder der JOIN-Klausel enthalten.
• Sie kann keiner Spalte entsprechen, die das Ergebnis einer Funktion (genauer: einer numerischen Berechnung, einer Aggregatfunktion oder einer benutzerdefinierten Funktion) sind. Notfalls kann die Funktion in der GROUP-Klausel wiederholt werden (siehe im letzten Beispiel Variante 2).

Dies entspricht der gleichen Einschränkung, die bei den unter „Ergebnis von Berechnungen“ im Kapitel Berechnete Spalten genannt ist.

Mit der Beispieldatenbank sind keine passenden Beispiele möglich; wir müssen uns deshalb auf fiktive Tabellen und Spalten beschränken:

 Die folgenden Abfragen sind nicht zulässig.

SELECT Spalte1,  Spalte2            FROM T GROUP BY Spalte1 + Spalte2;
SELECT Spalte1 + constant + Spalte2 FROM T GROUP BY Spalte1 + Spalte2;

 Die folgenden Abfragen sind zulässig.

SELECT Spalte1,  Spalte2            FROM T GROUP BY Spalte1, Spalte2;
SELECT Spalte1 + Spalte2            FROM T GROUP BY Spalte1, Spalte2;
SELECT Spalte1 + Spalte2            FROM T GROUP BY Spalte1+ Spalte2;
SELECT Spalte1 + Spalte2 + constant FROM T GROUP BY Spalte1, Spalte2;

• GROUP BY kann nicht benutzt werden in einem SELECT-Befehl mit folgenden Bedingungen:
  • Der Befehl enthält eine INTO-Klausel (d. h. er wird benutzt, um einzelne Zeilen zu verarbeiten – dies wird in dieser Einführung nicht erläutert).
  • Der Befehl enthält eine Unterabfrage oder eine VIEW, die selbst mit einer GROUP BY- oder HAVING-Klausel arbeitet.

• Jeder SELECT-Befehl darf einschließlich aller Unterabfragen höchstens eine GROUP BY-Klausel enthalten.

Zu dieser Bedingung ist den Autoren kein vernünftiges Beispiel eingefallen. Eines, das dafür konstruiert wurde, hat Firebird trotz klarer Verletzung ausgeführt, wahrscheinlich weil es sich wegen der anderen Bedingungen sowieso nur um jeweils eine Zeile handelte und keine Gruppierung erforderlich war.

Erweiterungen

Zwischensummen mit CUBE

Diese Erweiterung steht nur in manchen DBMS zur Verfügung. Es soll deshalb ein kurzer Hinweis genügen.

GROUP BY CUBE (<spaltenliste>)

Mit diesem Befehl werden zusätzlich zu den normalerweise von GROUP BY erstellten Zeilen auch Zwischensummen in das Ergebnis aufgenommen. Für jede mögliche Kombination von Spalten in der <spaltenliste> wird eine eigene GROUP BY-Summenzeile zurückgegeben.

Erläuterungen und Beispiele sind zu finden z. B. unter GROUP BY (Microsoft) und weiteren Links.

Gesamtsummen mit ROLLUP

Auch diese Erweiterung steht nur in manchen DBMS zur Verfügung. Es soll wiederum ein kurzer Hinweis genügen.

GROUP BY <spaltenliste> WITH ROLLUP

Mit diesem Befehl werden zusätzlich zu den von GROUP BY erstellten Zeilen auch Gesamtsummen in das Ergebnis aufgenommen.

Erläuterungen und Beispiele sind zu finden z. B. unter GROUP BY (MySQL, englisch).

Ergebnis mit HAVING einschränken

Diese Erweiterung ist eine selbständige Klausel des SELECT-Befehls und hat eigentlich nichts mit der GROUP BY-Klausel zu tun. Sie ist eine Ergänzung zu den Aggregatfunktionen, wird deshalb überwiegend bei Gruppierungen verwendet und folgt ggf. direkt danach.

GROUP BY <spaltenliste>
HAVING <bedingungen>

Dieser Befehl dient dazu, nicht alle Ergebnisse der Auswahl in die Ausgabe zu übernehmen, sondern nur diejenigen, die den zusätzlichen Bedingungen entsprechen.

Im folgenden Beispiel (vergleiche oben unter „Gruppierung bei einer Tabelle“) wird festgestellt, an welchen Orten sich genau eine Abteilung befindet.

SELECT Ort,
       COUNT(*) AS Anzahl
  FROM Abteilung
 GROUP BY Ort
 HAVING COUNT(*) = 1
 ORDER BY Ort;

Ausgabe

Herne      1

Bitte beachten Sie, dass der Alias-Name nicht verwendet werden kann, sondern die Aggregatfunktion bei HAVING erneut aufgeführt werden muss.

Hinweis: Die HAVING-Klausel wird als letzter Teil des SELECT-Befehls ausgeführt. Es ist deshalb zu empfehlen, alle Einschränkungen vorher zu regeln, z. B. als Teil von WHERE-Bedingungen. Nur wenn – wie bei Aggregatfunktionen – diese Einschränkung erst am Schluss geprüft werden kann, ist HAVING zu benutzen.

 Möglich, aber nicht günstig.

SELECT Abteilung_ID, COUNT(*)
  FROM MITARBEITER 
  GROUP BY Abteilung_ID
  HAVING Abteilung_ID < 3;

 Besseres Verfahren, weil weniger Datensätze geprüft werden müssen.

SELECT Abteilung_ID, COUNT(*)
  FROM MITARBEITER 
  WHERE Abteilung_ID < 3
  GROUP BY Abteilung_ID;

Zusammenfassung

In diesem Kapitel lernten wir Einzelheiten über die Gruppierung bei Abfragen.

• Dies wird meistens gleichzeitig mit ORDER BY und in Verbindung mit Aggregatfunktionen verwendet.
• Die Gruppierung ist auch über mehrere Tabellen hinweg möglich.
• Einige wichtige Einschränkungen sind zu beachten; vor allem sind die Felder aus der Spaltenliste in der Regel auch unter GROUP BY aufzuführen.

Die HAVING-Klausel kann das Abfrageergebnis einschränken, sollte aber zurückhaltend benutzt werden.

Übungen

Übung 1

Definitionen

Zur Lösung

Welche der folgenden Feststellungen sind wahr, welche sind falsch?

1. GROUP BY kann nur zusammen mit (mindestens) einer Spaltenfunktion benutzt werden.
2. GROUP BY kann nur auf „echte“ Spalten angewendet werden, nicht auf berechnete Spalten.
3. In der GROUP BY-Klausel kann ein Spaltenname ebenso angegeben werden wie ein Spalten-Alias.
4. Die WHERE-Klausel kommt vor der GROUP BY-Klausel.
5. Folgende Gruppierung nach den ersten zwei Ziffern der PLZ ist zulässig.
   

   select PLZ, COUNT(*)
   

     from Versicherungsnehmer vn
   

    group by SUBSTRING(vn.PLZ from 1 for 2)
   

    order by 1
   

6. HAVING darf nur zusammen mit einer Gruppierung verwendet werden.

Übung 2

Gruppierung für 1 Tabelle

Zur Lösung

Bestimmen Sie die Anzahl der Kunden (Versicherungsnehmer) in jedem Briefzentrum (d. h. die Ziffern 1 und 2 der PLZ).

Übung 3

Gruppierung für 1 Tabelle

Zur Lösung

Bestimmen Sie, wie viele Fahrzeuge in jedem Kreis angemeldet sind.

Übung 4

Gruppierung für mehrere Tabellen

Zur Lösung

Bestimmen Sie, wie viele Fahrzeugtypen pro Hersteller registriert sind, und nennen Sie Namen und Land der Hersteller.

Hinweis: Erstellen Sie zunächst eine Abfrage für Anzahl plus Hersteller-ID und verknüpfen Sie das Ergebnis mit der Tabelle Hersteller.

Übung 5

Gruppierung für mehrere Tabellen

Zur Lösung

Bestimmen Sie – gruppiert nach Jahr des Schadensfalls und Kreis des Fahrzeugs – die Anzahl der Schadensfälle. Es sollen bei den Fahrzeugen nur Schadensfälle mit einem Schuldanteil von mehr als 50 [Prozent] berücksichtigt werden.

Lösungen

Lösung zu Übung 1

Definitionen

Zur Übung

Richtig sind die Aussagen 3, 4. Falsch sind die Aussagen 1, 2, 5, 6.

Lösung zu Übung 2

Gruppierung für 1 Tabelle

Zur Übung

select SUBSTRING(vn.PLZ from 1 for 2), COUNT(*)
  from Versicherungsnehmer vn
 group by 1
 order by 1

Hinweis: Hierbei handelt es sich um die korrekte Version zur Frage 5 aus Übung 1.

Lösung zu Übung 3

Gruppierung für 1 Tabelle

Zur Übung

select SUBSTRING(Kennzeichen from 1 for POSITION('-', Kennzeichen)-1 ) as Kreis, 
       COUNT(*) as Anzahl
  from Fahrzeug fz
 group by 1
 order by 1;

Hinweis: Hierbei handelt es sich um die vollständige Version der letzten Übung zu „berechneten Spalten“.

Lösung zu Übung 4

Gruppierung für mehrere Tabellen

Zur Übung

select Name, Land, Anzahl
  from (
  select ft.Hersteller_ID as ID, Count(ft.Hersteller_ID) as Anzahl
    from Fahrzeugtyp ft
   group by ft.Hersteller_ID
       ) temp
       join Fahrzeughersteller fh
            on fh.ID = temp.ID
 order by Name;

Lösung zu Übung 5

Gruppierung für mehrere Tabellen

Zur Übung

select EXTRACT(YEAR from sf.Datum) as Jahr,
       SUBSTRING(Kennzeichen from 1 for POSITION('-', Kennzeichen)-1 ) as Kreis, 
       COUNT(*)
  from Zuordnung_SF_FZ zu 
       right join Fahrzeug     fz on fz.ID = zu.Fahrzeug_ID
       inner join Schadensfall sf on sf.ID = zu.Schadensfall_ID
 where zu.Schuldanteil > 50
 group by 1, 2
 order by 1, 2;

Erläuterungen: Die Tabelle der Zuordnungen ist kleiner als die diejenige der Fahrzeuge, und darauf bezieht sich die WHERE-Bedingung; deshalb ist sie als Haupttabelle am sinnvollsten. Wegen der Kennzeichen benötigen wir einen JOIN auf die Tabelle Fahrzeug. Wegen des Datums des Schadensfalls für die Gruppierung nach Jahr benötigen wir einen JOIN auf die Tabelle Schadensfall.

Siehe auch

Ergänzende Informationen sind in diesen Kapiteln zu finden:

• Datentypen informiert auch über besondere Sortierungen einer einzelnen Spalte.
• Mehrere Tabellen verknüpfen