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
BearbeitenDie 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
BearbeitenIm einfachsten Fall werden Daten nach einer Spalte gruppiert und gezählt.
Im folgenden Beispiel wird die Anzahl der Abteilungen für jeden Ort aufgeführt.
Die folgende Abfrage listet auf, wie viele Mitarbeiter es in den Abteilungen und Raumnummern gibt:
Am folgenden Beispiel wird die Gruppierung besonders deutlich.
Berechne die mittlere Schadenshöhe für die Schadensfälle mit und ohne Personenschäden.
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
BearbeitenEine 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.
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;
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:
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
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
BearbeitenWie 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:
SELECT Spalte1, Spalte2 FROM T GROUP BY Spalte1 + Spalte2;
SELECT Spalte1 + constant + 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 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
BearbeitenZwischensummen mit CUBE
BearbeitenDiese 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
BearbeitenAuch 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
BearbeitenDiese 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.
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.
SELECT Abteilung_ID, COUNT(*)
FROM MITARBEITER
GROUP BY Abteilung_ID
HAVING Abteilung_ID < 3;
Zusammenfassung
BearbeitenIn 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
Bearbeiten
Übung 1 | Definitionen | Zur Lösung |
Welche der folgenden Feststellungen sind wahr, welche sind falsch?
- GROUP BY kann nur zusammen mit (mindestens) einer Spaltenfunktion benutzt werden.
- GROUP BY kann nur auf „echte“ Spalten angewendet werden, nicht auf berechnete Spalten.
- In der GROUP BY-Klausel kann ein Spaltenname ebenso angegeben werden wie ein Spalten-Alias.
- Die WHERE-Klausel kommt vor der GROUP BY-Klausel.
- 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
- 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ö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
BearbeitenErgänzende Informationen sind in diesen Kapiteln zu finden:
- Datentypen informiert auch über besondere Sortierungen einer einzelnen Spalte.
- Mehrere Tabellen verknüpfen