SQL Lektion 4: GROUP BY und HAVING

Lösung geprüft

Die Aufgabe der heutigen Stunde ist die vierte SQL-Lektion: GROUP BY und HAVING. In Lektion 2 haben wir gelernt, einen Durchschnitt über alle Zeilen zu bilden. Jetzt bilden wir ihn gruppenweise – pro Region, pro Fahrer, pro Kategorie.

Die Aufgabenstellung lautet

a) Bearbeiten Sie die vierte Lektion und erstellen Sie in ihrem OneNote-Heft eine kompakte übersichtliche Kurzzusammenfassung der SQL-Befehle.
b) Bearbeiten Sie die Übung 4

Zusatzaufgaben
a) Ermitteln Sie die ersten 5 Weltmeisterfahrer, die insgesamt am meisten Punkte haben.
b) Ermitteln Sie alle Fahrer und deren WM-Gesamtpunkte, die mit A oder K anfangen und deren Punktezahl über dem Durchschnitt aller WM-Punkte liegt.
c) Für die Datenbank aus Übung 3: Geben Sie den Vornamen, Nachnamen und die Gesamtanzahl der verkauften Artikel an, von den ersten drei Mitarbeitern, die am meisten Artikel verkauft haben.

Teil b) der Aufgabe – Bearbeiten Sie die Übung 4 – liegt auf der Tutorial-Seite und ist im OneNote nicht abgedruckt. Hier stehen die Zusammenfassung und die drei Zusatzaufgaben, die im OneNote ausgeschrieben sind.

GROUP BY – Zeilen zu Gruppen zusammenfassen

SQL

/* meininfokurs.cmrg.site */

-- Zusammenfassung der vierten SQL-Lektion: GROUP BY und HAVING
-- @meininfokurs.cmrg.site

-- Wie viele Titel hat jeder Fahrer?
SELECT   Fahrerweltmeister, COUNT(*) AS Titel
FROM     WMTitel
GROUP BY Fahrerweltmeister;

-- Nur die Fahrer mit mehr als zwei Titeln
SELECT   Fahrerweltmeister, COUNT(*) AS Titel
FROM     WMTitel
GROUP BY Fahrerweltmeister
HAVING   COUNT(*) > 2
ORDER BY Titel DESC;

Ohne GROUP BY liefert COUNT(*) eine einzige Zahl für die ganze Tabelle. Mit GROUP BY Fahrerweltmeister werden erst alle Zeilen desselben Fahrers zu einer Gruppe zusammengefasst, und dann wird pro Gruppe gezählt.

Die Regel, an der alle scheitern

In SELECT darf nur stehen, wonach gruppiert wurde – oder eine Aggregatfunktion.

-- FALSCH: Saison steht weder in GROUP BY noch in einer Funktion
SELECT   Fahrerweltmeister, Saison, COUNT(*)
FROM     WMTitel
GROUP BY Fahrerweltmeister;

Der Grund ist derselbe wie in Lektion 2: eine Gruppe fasst mehrere Zeilen zu einer zusammen. Schumacher hat sieben Saisons – welche soll da stehen? Entweder man gruppiert auch danach, oder man nimmt MAX(Saison), oder man lässt die Spalte weg.

WHERE oder HAVING?

	WHERE	HAVING
Filtert	einzelne Zeilen	fertige Gruppen
Läuft	vor dem Gruppieren	nach dem Gruppieren
Aggregatfunktionen	verboten	erlaubt
Beispiel	`WHERE WM_Punkte > 50`	`HAVING COUNT(*) > 2`

Beide dürfen in derselben Abfrage vorkommen und tun dann unterschiedliche Dinge:

SELECT   Fahrerweltmeister, COUNT(*) AS Titel
FROM     WMTitel
WHERE    WM_Punkte > 50            -- erst: nur starke Saisons behalten
GROUP BY Fahrerweltmeister         -- dann: nach Fahrer gruppieren
HAVING   COUNT(*) > 1;             -- zuletzt: nur Fahrer mit mehr als einer

Die vollständige Reihenfolge

SELECT   ...
FROM     ...
WHERE    ...      -- Zeilen filtern
GROUP BY ...      -- gruppieren
HAVING   ...      -- Gruppen filtern
ORDER BY ...      -- sortieren
LIMIT    ...      -- abschneiden

Diese Reihenfolge ist auch die Abarbeitungsreihenfolge der Datenbank. Wer sie kennt, versteht sofort, warum Aggregatfunktionen in WHERE nicht gehen und in HAVING schon.

Zusatzaufgabe a) – Die Top 5 nach Gesamtpunkten

SELECT   Fahrerweltmeister, SUM(WM_Punkte) AS Gesamtpunkte
FROM     WMTitel
GROUP BY Fahrerweltmeister
ORDER BY Gesamtpunkte DESC
LIMIT 5;

„Insgesamt" ist das Stichwort für SUM zusammen mit GROUP BY: ein Fahrer kann mehrere Titel haben, und seine Punkte sollen über alle Saisons addiert werden. Ohne das GROUP BY bekäme man eine einzige Summe über alle Fahrer.

Das ORDER BY muss vor dem LIMIT stehen, sonst schneidet man fünf beliebige Fahrer ab statt der besten fünf.

Zusatzaufgabe b) – A oder K und über dem Durchschnitt

SELECT   Fahrerweltmeister, SUM(WM_Punkte) AS Gesamtpunkte
FROM     WMTitel
WHERE    Fahrerweltmeister LIKE 'A%' OR Fahrerweltmeister LIKE 'K%'
GROUP BY Fahrerweltmeister
HAVING   SUM(WM_Punkte) > (SELECT AVG(WM_Punkte) FROM WMTitel)
ORDER BY Gesamtpunkte DESC;

Hier sind alle Bausteine der Lektion auf einmal drin:

WHERE … LIKE 'A%' filtert die Zeilen nach dem Anfangsbuchstaben – % steht für den Rest des Namens
GROUP BY fasst pro Fahrer zusammen
HAVING vergleicht die Gruppensumme mit dem Durchschnitt

Die Bedingung mit den Anfangsbuchstaben muss ins WHERE, weil sie sich auf einzelne Zeilen bezieht. Die Bedingung mit der Summe muss ins HAVING, weil es die Summe vor dem Gruppieren noch nicht gibt. Das ist die Aufgabe, an der man den Unterschied wirklich versteht.

Aufpassen bei der Frage, womit verglichen wird: die Aufgabe sagt „über dem Durchschnitt aller WM-Punkte". Das ist der Durchschnitt über alle einzelnen Saison-Einträge, nicht der Durchschnitt der Fahrer-Gesamtsummen. Deshalb steht in der Unterabfrage AVG(WM_Punkte) ohne GROUP BY.

Zusatzaufgabe c) – Die drei besten Verkäufer

Die Datenbank aus Übung 3 liegt auf der Tutorial-Seite und ist im OneNote nicht abgedruckt. Ich schreibe die Abfrage gegen die Tabellen mitarbeiter(id, vorname, nachname) und verkaeufe(id, mitarbeiter_id, anzahl). Heißen die Tabellen bei euch anders, tausch die Namen aus – der Aufbau bleibt.

SELECT   m.vorname, m.nachname, SUM(v.anzahl) AS VerkaufteArtikel
FROM     mitarbeiter AS m
INNER JOIN verkaeufe AS v ON m.id = v.mitarbeiter_id
GROUP BY m.id, m.vorname, m.nachname
ORDER BY VerkaufteArtikel DESC
LIMIT 3;

Diese Abfrage kombiniert Lektion 3 und 4: erst der JOIN, damit man Namen und Verkäufe zusammen hat, dann GROUP BY pro Mitarbeiter, dann sortieren und die ersten drei nehmen.

Im GROUP BY stehen alle drei Spalten, die auch im SELECT vorkommen – das ist die Regel von oben. m.id gehört unbedingt dazu: gäbe es zwei Mitarbeiter mit demselben Namen, würden ihre Verkäufe sonst zusammengezählt.