Level 5: Eine Tabelle zusammenfassen: Aggregate

Lektion

Bisher ergab jede Zeile der Tabelle eine Ergebniszeile. Eine Aggregatfunktion macht das Gegenteil: Sie fasst mehrere Zeilen zu einem einzigen Wert zusammen. COUNT zählt, SUM addiert, AVG berechnet den Durchschnitt, MIN und MAX liefern den kleinsten und den größten Wert.

Ohne weitere Angabe gilt das Aggregat für die ganze Tabelle, und das Ergebnis passt in eine einzige Zeile. SELECT COUNT(*), AVG(rating) FROM movies beantwortet zwei Fragen auf einmal: wie viele Filme und welche Durchschnittsbewertung.

COUNT hat zwei Formen. COUNT(*) zählt alle Zeilen. COUNT(column) zählt nur die Zeilen, in denen die Spalte gefüllt ist: Bei den Filmen ergibt COUNT(*) 10, aber COUNT(director_id) 9, weil Deep Current keinen Regisseur hat. Die anderen Aggregate ignorieren NULL ebenfalls: AVG bildet den Durchschnitt nur der bekannten Werte.

MIN und MAX funktionieren auch mit Texten (alphabetische Reihenfolge) und mit Datumsangaben (das älteste, das neueste).

Das WHERE wirkt vor dem Aggregat: Man wählt zuerst die Zeilen aus und fasst sie dann zusammen. SELECT AVG(grade) FROM grades WHERE subject = 'math' liefert den Durchschnitt nur der Mathenoten. Wenn keine Zeile den Filter passiert, liefert COUNT 0, aber SUM, AVG, MIN und MAX liefern NULL.

ROUND(value, n) rundet auf n Nachkommastellen: ROUND(AVG(grade), 1). Ein Durchschnitt hat oft viele Nachkommastellen: Runde ihn für die Anzeige.

Eine wichtige Grenze: Ohne Gruppierung kann man keine gewöhnliche Spalte neben einem Aggregat anzeigen. SELECT title, MAX(rating) ergibt im Standard-SQL keinen Sinn: MAX liefert einen einzigen Wert, aber welcher Titel soll daneben stehen? Level 14 zeigt die richtige Methode.

Syntax

SELECT COUNT(*) AS anzahl, AVG(spalte) AS mittel, MAX(spalte) AS maximum
FROM meine_tabelle
WHERE …;

Kommentiertes Beispiel

SELECT COUNT(*) AS nb_movies,
       AVG(rating) AS avg_rating,
       MAX(duration) AS longest
FROM movies;

Die ganze Tabelle wird in einer einzigen Zeile zusammengefasst: 10 Filme, ihre Durchschnittsbewertung und die Dauer des längsten.

Ergebnis des Beispiels

Das Wichtigste

• Ein Aggregat ohne GROUP BY liefert eine einzige Zeile.
• COUNT(*) zählt Zeilen, COUNT(spalte) nicht leere Werte.
• WHERE filtert vor der Berechnung.

Häufige Fallen

• Eine einfache Spalte und ein Aggregat ohne Gruppierung mischen (SELECT title, MAX(rating)) ist im Standard-SQL ein Fehler, auch wenn SQLite es akzeptiert.
• AVG über eine Spalte mit NULL-Werten mittelt nur die bekannten Werte.

SQLite-Besonderheit

SQLite akzeptiert SELECT title, MAX(rating) FROM movies und liefert den Titel des bestbewerteten Films: eine „nackte Spalte“, eine Besonderheit von SQLite. PostgreSQL, SQL Server und Oracle lehnen diese Abfrage ab. Die portable Methode (eine Unterabfrage) kommt in Level 14.

Die 5 Übungen des Levels

1. Geführt · Wie viele Bücher enthält die Tabelle books? (Tabelle: books)
2. Training · Wie viele Plätze wurden insgesamt verkauft, über alle Flüge? (Tabelle: flights)
3. Training · Zeige das Datum der ersten Ausleihe (first_loan) und das der letzten Ausleihe (last_loan). (Tabelle: loans)
4. Training · Wie viele Kontakte haben eine E-Mail-Adresse? (Tabelle: contacts)
5. Herausforderung · Zeige für die Mathenoten in einer einzigen Zeile: die Anzahl der Noten (nb_grades), ihren auf eine Nachkommastelle gerundeten Durchschnitt (avg_math) und den Abstand zwischen der besten und der schlechtesten Note (spread). (Tabelle: grades)

In SpeedQL wird jede Abfrage sofort geprüft: Ihr Ergebnis wird mit dem erwarteten verglichen, dann läuft die Abfrage noch einmal auf einer versteckten Kontrolldatenbank. Zu jeder Übung gibt es schriftliche Hinweise, die du nur bei Bedarf aufrufst.