SQL Lektion 2: Aggregatfunktionen

Lösung geprüft

Die Aufgabe der heutigen Stunde ist die zweite SQL-Lektion! Hier kommen die Aggregatfunktionen dazu – Befehle, die nicht mehr einzelne Zeilen liefern, sondern aus vielen Zeilen einen Wert ausrechnen.

Die Aufgabenstellung lautet

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

Zusatzaufgabe 1Z: Gebe von Europa und Asien das pro Kopf Durchschnittseinkommen an.
Zusatzaufgabe 2Z: Welche Länder liegen über dem durchschnittlichen pro Kopf Einkommen?

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

Die fünf Aggregatfunktionen

Funktion	Was sie macht	Beispiel
`COUNT()`	zählt Zeilen	`COUNT(*)`
`SUM()`	addiert alle Werte	`SUM(WM_Punkte)`
`AVG()`	Durchschnitt	`AVG(WM_Punkte)`
`MAX()`	größter Wert	`MAX(WM_Punkte)`
`MIN()`	kleinster Wert	`MIN(WM_Punkte)`

SQL

/* meininfokurs.cmrg.site */

-- Zusammenfassung der zweiten SQL-Lektion
-- @meininfokurs.cmrg.site

-- Wie viele Weltmeistertitel gab es insgesamt?
SELECT COUNT(*) AS Anzahl
FROM   WMTitel;

-- Wie viele verschiedene Weltmeister waren es?
SELECT COUNT(DISTINCT Fahrerweltmeister) AS VerschiedeneFahrer
FROM   WMTitel;

-- Hoechste, niedrigste und durchschnittliche Punktzahl auf einmal
SELECT MAX(WM_Punkte) AS Bestwert,
       MIN(WM_Punkte) AS Tiefstwert,
       AVG(WM_Punkte) AS Durchschnitt,
       SUM(WM_Punkte) AS Gesamtpunkte
FROM   WMTitel;

COUNT(*) gegen COUNT(spalte)

Der Unterschied ist klein, aber prüfungsrelevant:

Schreibweise	Zählt
`COUNT(*)`	alle Zeilen, auch solche mit NULL-Werten
`COUNT(spalte)`	nur Zeilen, in denen diese Spalte nicht NULL ist
`COUNT(DISTINCT spalte)`	verschiedene Werte, ohne Doppelte

Das gilt auch für die anderen Funktionen: AVG lässt NULL-Werte einfach weg und teilt nur durch die Anzahl der vorhandenen Werte. Das ist meistens genau richtig – man sollte es aber wissen.

Die häufigste Falle

-- FALSCH: gemischt geht nicht
SELECT Fahrerweltmeister, MAX(WM_Punkte)
FROM   WMTitel;

Eine Aggregatfunktion fasst alle Zeilen zu einer zusammen. Ein normales Attribut wie Fahrerweltmeister hat aber pro Zeile einen eigenen Wert – welchen sollte die Datenbank nehmen? MySQL lässt das in manchen Einstellungen durchgehen und liefert dann irgendeinen Namen, der gar nicht zum Maximum gehören muss. Richtig löst man das mit ORDER BY … LIMIT 1 oder mit GROUP BY aus Lektion 4.

-- RICHTIG: wer hatte die meisten Punkte?
SELECT Fahrerweltmeister, WM_Punkte
FROM   WMTitel
ORDER BY WM_Punkte DESC
LIMIT 1;

Die Zusatzaufgaben

Die Tabelle cia aus dem Tutorial ist im OneNote nicht abgedruckt – dort steht nur SELECT * FROM cia. Ich schreibe die Abfragen deshalb gegen die Spaltennamen name, region, einkommen und bevoelkerung. Heißen die Spalten in eurer Datenbank anders, musst du nur die Namen austauschen – die Struktur der Abfrage bleibt gleich.

Aufgabe 1Z – Durchschnittseinkommen Europa und Asien

SELECT region, AVG(einkommen) AS ProKopfDurchschnitt
FROM   cia
WHERE  region IN ('Europa', 'Asien')
GROUP BY region;

Zwei Bausteine: das IN aus Lektion 1 grenzt auf die beiden Regionen ein, das GROUP BY sorgt dafür, dass man zwei Werte bekommt statt einem gemeinsamen Durchschnitt über beide Regionen zusammen.

Will man wirklich nur eine Zahl für beide zusammen, lässt man das GROUP BY weg:

SELECT AVG(einkommen) AS ProKopfDurchschnitt
FROM   cia
WHERE  region IN ('Europa', 'Asien');

Die Aufgabe sagt „von Europa und Asien" – ich lese das als zwei getrennte Werte, deshalb steht bei mir das GROUP BY drin. Wenn du es anders liest, schreib deine Lesart dazu.

Aufgabe 2Z – Länder über dem Durchschnitt

SELECT name, einkommen
FROM   cia
WHERE  einkommen > (SELECT AVG(einkommen) FROM cia)
ORDER BY einkommen DESC;

Hier steht zum ersten Mal eine Abfrage in der Abfrage. Der Teil in Klammern wird zuerst ausgeführt und liefert genau eine Zahl – den Gesamtdurchschnitt. Mit der wird dann jede Zeile verglichen.

Warum geht es nicht einfacher? Weil das hier nicht funktioniert:

-- FALSCH: Aggregatfunktionen sind in WHERE verboten
SELECT name, einkommen
FROM   cia
WHERE  einkommen > AVG(einkommen);

Der Grund liegt in der Arbeitsreihenfolge der Datenbank: WHERE entscheidet Zeile für Zeile, ob sie überhaupt dazugehört. Zu diesem Zeitpunkt steht der Durchschnitt noch gar nicht fest, denn dafür bräuchte man ja schon alle Zeilen. Deshalb die Unterabfrage, die vorher durchläuft.

Solche geschachtelten Abfragen sind eigentlich erst Thema von Lektion 5 – die Zusatzaufgabe greift hier also schon vor. Das ist kein Fehler in der Aufgabe, sondern Absicht: man merkt so selbst, wo die Grenzen der bisherigen Mittel liegen.