Zum Inhalt springen
aviral gupta
Kontakt aufnehmen

// B3.4 · ca. 30 Min. · Einstieg

Aggregatfunktionen und GROUP BY

Nach dieser Lektion beantworten Sie „wie viele“ und „wie viel“ in einer Abfrage: für eine ganze Tabelle oder je Genre, Mitglied oder Stadt.

Lektion 4 von 6 in B3 Joins und Aggregate

Danach können Sie

  • Eine Tabelle mit count, sum, avg, min und max zusammenfassen und einen Durchschnitt runden
  • Zeilen mit GROUP BY nach einer oder zwei Spalten gruppieren, auch nach einem Join
  • Aggregate über NULL-Werte und über keine Zeilen vorhersagen und Gruppen nach einem Aggregat sortieren

  1. Aufwärmen · Aufgabe 1 von 7

    Aufwärmen: Die Tabelle books hat sechs Zeilen. Was liefert diese Abfrage?

    SELECT count(*) FROM books;

  2. Vorhersagen · Aufgabe 2 von 7

    Sagen Sie es voraus, bevor Sie weiterlesen. Die sechs Bücher haben die Genres sci-fi (drei Bücher), classic, fiction und fantasy. Wie viele Zeilen liefert dies?

    SELECT genre, count(*)
FROM books
GROUP BY genre;

  3. Üben · Aufgabe 3 von 7

    Fünf Ausleihen stammen von den Mitgliedern 1, 1, 2, 3 und 2. Ergänzen Sie das Schlüsselwort, damit die Abfrage die verschiedenen Mitglieder zählt, die ausgeliehen haben, nicht die Ausleihen.

    SELECT count(____ member_id) FROM loans;
    SELECT count( member_id) FROM loans;

  4. Üben · Aufgabe 4 von 7

    Die Bücher haben 2, 1, 1, 1, 3 und 1 Exemplare, in vier Genres. Ordnen Sie jedem Ausdruck seinen Wert zu.

  5. Üben · Aufgabe 5 von 7

    Bringen Sie die Zeilen in die Reihenfolge, in der diese Abfrage sie liefert: SELECT genre, sum(copies) AS copies FROM books GROUP BY genre ORDER BY copies DESC, genre;

    1. 1.fiction (1 Exemplar)
    2. 2.classic (1 Exemplar)
    3. 3.sci-fi (4 Exemplare)
    4. 4.fantasy (3 Exemplare)

  6. Denksport · Aufgabe 6 von 7

    Knobelaufgabe. Das neueste Buch ist von 1987, kein Buch erfüllt also das WHERE. Was liefert dies?

    SELECT count(*), sum(copies)
FROM books
WHERE year > 2000;

  7. Anwenden · Aufgabe 7 von 7

    Mini-Aufgabe. Schreiben Sie mit der Bibliothek des Beispiels eine Abfrage: für jede Stadt die Zahl der Mitglieder und das Datum, an dem das erste von ihnen beitrat. Zeigen Sie eine fehlende Stadt als (unknown), stellen Sie die größte Stadt nach vorn und entscheiden Sie Gleichstände nach dem Beitrittsdatum. Sagen Sie vor dem Ausführen voraus, wie viele Zeilen sie liefert.

    Prüfen Sie Ihr Ergebnis anhand dieser Liste

Selbst programmieren

Lesen Sie das ausgearbeitete Beispiel und lösen Sie dann die Übungen. Ihr Code läuft in Ihrem Browser oder auf Ihrem Computer und wird nie hochgeladen.

Ausgearbeitetes Beispiel

Die Bibliothek zusammengefasst

setup.sql lädt die Bibliothek aus Modul B1: Bücher, Mitglieder und Ausleihen. main.sql fasst den Katalog in einer Zeile zusammen, zählt die Ausleihen auf drei Arten, gruppiert dann die Bücher nach Genre und, nach einem Join, die Ausleihen nach Genre. Auf Ihrem Computer: psql -X -q -v ON_ERROR_STOP=1 -f setup.sql -f main.sql.

main.sql

-- 1. The whole catalogue in one row.
SELECT count(*) AS books,
       sum(copies) AS copies,
       round(avg(copies), 2) AS avg_copies,
       min(year) AS oldest,
       max(year) AS newest
FROM books;

-- 2. Three ways to count the loans.
SELECT count(*) AS loans,
       count(returned_on) AS returned,
       count(DISTINCT member_id) AS borrowers
FROM loans;

-- 3. One row per genre, most copies first.
SELECT genre, count(*) AS books, sum(copies) AS copies
FROM books
GROUP BY genre
ORDER BY copies DESC, genre;

-- 4. Loans per genre: join first, then group.
SELECT b.genre, count(*) AS loans, count(DISTINCT l.book_id) AS titles
FROM loans l
JOIN books b ON b.id = l.book_id
GROUP BY b.genre
ORDER BY loans DESC;

setup.sql

CREATE TABLE books (
  id integer,
  title text,
  author text,
  year integer,
  genre text,
  copies integer
);

INSERT INTO books VALUES
  (1, 'Dune', 'Frank Herbert', 1965, 'sci-fi', 2),
  (2, 'Emma', 'Jane Austen', 1815, 'classic', 1),
  (3, 'Kindred', 'Octavia E. Butler', 1979, 'sci-fi', 1),
  (4, 'Beloved', 'Toni Morrison', 1987, 'fiction', 1),
  (5, 'The Hobbit', 'J. R. R. Tolkien', 1937, 'fantasy', 3),
  (6, 'Neuromancer', 'William Gibson', 1984, 'sci-fi', 1);

CREATE TABLE members (
  id integer,
  name text,
  email text,
  city text,
  joined date
);

INSERT INTO members VALUES
  (1, 'Ada', 'ada@example.com', 'Berlin', '2025-01-15'),
  (2, 'Grace', 'grace@example.com', 'Munich', '2025-03-02'),
  (3, 'Linus', NULL, 'Berlin', '2026-02-10'),
  (4, 'Margaret', 'margaret@example.com', NULL, '2026-05-20');

CREATE TABLE loans (
  id integer,
  book_id integer,
  member_id integer,
  loaned_on date,
  returned_on date
);

INSERT INTO loans VALUES
  (1, 1, 1, '2026-09-01', '2026-09-10'),
  (2, 3, 1, '2026-09-12', NULL),
  (3, 2, 2, '2026-09-05', '2026-09-20'),
  (4, 1, 3, '2026-09-15', NULL),
  (5, 6, 2, '2026-09-18', NULL);

Ausführen mit

psql -X -q -v ON_ERROR_STOP=1 -f setup.sql -f main.sql

Ausgabe

 books | copies | avg_copies | oldest | newest
-------+--------+------------+--------+--------
     6 |      9 |       1.50 |   1815 |   1987
(1 row)

 loans | returned | borrowers
-------+----------+-----------
     5 |        2 |         3
(1 row)

  genre  | books | copies
---------+-------+--------
 sci-fi  |     3 |      4
 fantasy |     1 |      3
 classic |     1 |      1
 fiction |     1 |      1
(4 rows)

  genre  | loans | titles
---------+-------+--------
 sci-fi  |     4 |      3
 classic |     1 |      1
(2 rows)
  • Die erste Abfrage hat Aggregate und kein GROUP BY, also bilden alle sechs Bücher eine Gruppe, und das Ergebnis ist eine Zeile.
  • count(returned_on) überspringt die drei offenen Ausleihen, deren returned_on NULL ist; count(DISTINCT member_id) zählt die Mitglieder 1, 2 und 3 je einmal.
  • GROUP BY genre liefert eine Zeile je Genre; classic und fiction haben gleich viele Exemplare, also ordnet sie der zweite Sortierschlüssel, genre.
  • In der letzten Abfrage läuft der Join zuerst: sci-fi hat vier Ausleihen von drei verschiedenen Büchern, weil Dune zweimal ausgeliehen wurde.
Ändern und ausführen

Tab rückt ein, Umschalt+Tab rückt aus. Um den Editor mit der Tastatur zu verlassen, drücken Sie Esc und dann Tab.

Beim ersten Ausführen lädt Ihr Browser PostgreSQL herunter (bis zu 10.1 MB) und speichert es im Cache. Jeder Lauf beginnt mit einer leeren Datenbank. Ihr Code bleibt auf Ihrem Gerät.

Übungen

Übung 1 von 2

Ein Katalog je Genre und eine Mitgliederliste je Stadt

Schreiben Sie zwei Abfragen. Erstens: für jedes Genre das Genre, die Zahl der Bücher (books), die Exemplare insgesamt (copies) und das Jahr des ältesten Buchs (oldest), nach Genre sortiert. Zweitens: für jede Stadt der Mitglieder die Stadt, die Zahl der Mitglieder (members) und das späteste Beitrittsdatum (last_joined), nach Stadt sortiert; die Mitglieder ohne Stadt bilden eine eigene Gruppe.

Tab rückt ein, Umschalt+Tab rückt aus. Um den Editor mit der Tastatur zu verlassen, drücken Sie Esc und dann Tab.

Beim ersten Ausführen lädt Ihr Browser PostgreSQL herunter (bis zu 10.1 MB) und speichert es im Cache. Jeder Lauf beginnt mit einer leeren Datenbank. Ihr Code bleibt auf Ihrem Gerät.

Hinweise
  1. Hinweis 1

    GROUP BY genre liefert eine Zeile je Genre; count(*), sum(copies) und min(year) werden dann je Genre berechnet.

  2. Hinweis 2

    Benennen Sie die Spalten mit AS: count(*) AS books, sum(copies) AS copies, min(year) AS oldest.

  3. Hinweis 3

    Die zweite Abfrage hat dieselbe Form: GROUP BY city, count(*) AS members, max(joined) AS last_joined, ORDER BY city. Die NULL-Stadt kommt zuletzt.

Eine Lösung zeigen

Ein möglicher Lösungsweg. Ihrer kann anders aussehen und trotzdem alle Prüfungen bestehen.

SELECT genre, count(*) AS books, sum(copies) AS copies, min(year) AS oldest
FROM books
GROUP BY genre
ORDER BY genre;

SELECT city, count(*) AS members, max(joined) AS last_joined
FROM members
GROUP BY city
ORDER BY city;
Auf dem eigenen Computer ausführen

Installieren Sie PostgreSQL 18 oder neuer. Speichern Sie diese Dateien in einem Ordner, öffnen Sie dort ein Terminal und führen Sie die Befehle unten aus.

main.sql

-- setup.sql has loaded books, members and loans.
SELECT genre, title, year, copies FROM books ORDER BY genre;

test.sql

-- test: Eine Zeile je Genre mit Büchern, Exemplaren und ältestem Jahr, dann eine Zeile je Stadt, die unbekannte Stadt zuletzt
-- output:
--   genre  | books | copies | oldest
-- ---------+-------+--------+--------
--  classic |     1 |      1 |   1815
--  fantasy |     1 |      3 |   1937
--  fiction |     1 |      1 |   1987
--  sci-fi  |     3 |      4 |   1965
-- (4 rows)
--
--   city  | members | last_joined
-- --------+---------+-------------
--  Berlin |       2 | 2026-02-10
--  Munich |       1 | 2025-03-02
--         |       1 | 2026-05-20
-- (3 rows)

setup.sql

CREATE TABLE books (
  id integer,
  title text,
  author text,
  year integer,
  genre text,
  copies integer
);

INSERT INTO books VALUES
  (1, 'Dune', 'Frank Herbert', 1965, 'sci-fi', 2),
  (2, 'Emma', 'Jane Austen', 1815, 'classic', 1),
  (3, 'Kindred', 'Octavia E. Butler', 1979, 'sci-fi', 1),
  (4, 'Beloved', 'Toni Morrison', 1987, 'fiction', 1),
  (5, 'The Hobbit', 'J. R. R. Tolkien', 1937, 'fantasy', 3),
  (6, 'Neuromancer', 'William Gibson', 1984, 'sci-fi', 1);

CREATE TABLE members (
  id integer,
  name text,
  email text,
  city text,
  joined date
);

INSERT INTO members VALUES
  (1, 'Ada', 'ada@example.com', 'Berlin', '2025-01-15'),
  (2, 'Grace', 'grace@example.com', 'Munich', '2025-03-02'),
  (3, 'Linus', NULL, 'Berlin', '2026-02-10'),
  (4, 'Margaret', 'margaret@example.com', NULL, '2026-05-20');

CREATE TABLE loans (
  id integer,
  book_id integer,
  member_id integer,
  loaned_on date,
  returned_on date
);

INSERT INTO loans VALUES
  (1, 1, 1, '2026-09-01', '2026-09-10'),
  (2, 3, 1, '2026-09-12', NULL),
  (3, 2, 2, '2026-09-05', '2026-09-20'),
  (4, 1, 3, '2026-09-15', NULL),
  (5, 6, 2, '2026-09-18', NULL);

psql führt die Dateien auf Ihrem eigenen PostgreSQL-Server aus. Nehmen Sie eine leere Datenbank, die Sie wegwerfen können: Hängen Sie -d und ihren Namen an den Befehl an.

Programm ausführen:

psql -X -q -v ON_ERROR_STOP=1 -f setup.sql -f main.sql

Für die Prüfungen gibt es auf Ihrem Computer noch keinen Befehl. Sie stehen in test.sql: Jede Prüfabfrage gibt t aus, wenn sie besteht.

Übung 2 von 2

Ausleihen je Mitglied, mit Namen

Die Vorlage zählt die Ausleihen je member_id. Machen Sie daraus einen Bericht, den eine Bibliothekarin lesen kann: Verbinden Sie members, um den Namen zu zeigen, und ergänzen Sie die Zahl der noch offenen Ausleihen (open) und das Datum der letzten Ausleihe (last_loan). Sortieren Sie nach Namen. Eine offene Ausleihe hat kein returned_on.

Tab rückt ein, Umschalt+Tab rückt aus. Um den Editor mit der Tastatur zu verlassen, drücken Sie Esc und dann Tab.

Beim ersten Ausführen lädt Ihr Browser PostgreSQL herunter (bis zu 10.1 MB) und speichert es im Cache. Jeder Lauf beginnt mit einer leeren Datenbank. Ihr Code bleibt auf Ihrem Gerät.

Hinweise
  1. Hinweis 1

    JOIN members m ON m.id = l.member_id, dann nach Mitglied gruppieren: GROUP BY m.id, m.name.

  2. Hinweis 2

    m.name steht in der SELECT-Liste, also muss es auch in GROUP BY stehen. m.id hält zwei Mitglieder mit gleichem Namen auseinander.

  3. Hinweis 3

    count(*) zählt alle Ausleihen, count(l.returned_on) nur die zurückgegebenen; die Differenz sind die offenen. max(l.loaned_on) ist die letzte Ausleihe.

Eine Lösung zeigen

Ein möglicher Lösungsweg. Ihrer kann anders aussehen und trotzdem alle Prüfungen bestehen.

SELECT m.name,
       count(*) AS loans,
       count(*) - count(l.returned_on) AS open,
       max(l.loaned_on) AS last_loan
FROM loans l
JOIN members m ON m.id = l.member_id
GROUP BY m.id, m.name
ORDER BY m.name;
Auf dem eigenen Computer ausführen

Installieren Sie PostgreSQL 18 oder neuer. Speichern Sie diese Dateien in einem Ordner, öffnen Sie dort ein Terminal und führen Sie die Befehle unten aus.

main.sql

-- Loans per member: only ids so far.
SELECT member_id, count(*) AS loans
FROM loans
GROUP BY member_id
ORDER BY member_id;

test.sql

-- test: Ada, Grace und Linus, jeweils mit Ausleihen, offenen Ausleihen und dem Datum der letzten Ausleihe
-- output:
--  name  | loans | open | last_loan
-- -------+-------+------+------------
--  Ada   |     2 |    1 | 2026-09-12
--  Grace |     2 |    1 | 2026-09-18
--  Linus |     1 |    1 | 2026-09-15
-- (3 rows)

setup.sql

CREATE TABLE books (
  id integer,
  title text,
  author text,
  year integer,
  genre text,
  copies integer
);

INSERT INTO books VALUES
  (1, 'Dune', 'Frank Herbert', 1965, 'sci-fi', 2),
  (2, 'Emma', 'Jane Austen', 1815, 'classic', 1),
  (3, 'Kindred', 'Octavia E. Butler', 1979, 'sci-fi', 1),
  (4, 'Beloved', 'Toni Morrison', 1987, 'fiction', 1),
  (5, 'The Hobbit', 'J. R. R. Tolkien', 1937, 'fantasy', 3),
  (6, 'Neuromancer', 'William Gibson', 1984, 'sci-fi', 1);

CREATE TABLE members (
  id integer,
  name text,
  email text,
  city text,
  joined date
);

INSERT INTO members VALUES
  (1, 'Ada', 'ada@example.com', 'Berlin', '2025-01-15'),
  (2, 'Grace', 'grace@example.com', 'Munich', '2025-03-02'),
  (3, 'Linus', NULL, 'Berlin', '2026-02-10'),
  (4, 'Margaret', 'margaret@example.com', NULL, '2026-05-20');

CREATE TABLE loans (
  id integer,
  book_id integer,
  member_id integer,
  loaned_on date,
  returned_on date
);

INSERT INTO loans VALUES
  (1, 1, 1, '2026-09-01', '2026-09-10'),
  (2, 3, 1, '2026-09-12', NULL),
  (3, 2, 2, '2026-09-05', '2026-09-20'),
  (4, 1, 3, '2026-09-15', NULL),
  (5, 6, 2, '2026-09-18', NULL);

psql führt die Dateien auf Ihrem eigenen PostgreSQL-Server aus. Nehmen Sie eine leere Datenbank, die Sie wegwerfen können: Hängen Sie -d und ihren Namen an den Befehl an.

Programm ausführen:

psql -X -q -v ON_ERROR_STOP=1 -f setup.sql -f main.sql

Für die Prüfungen gibt es auf Ihrem Computer noch keinen Befehl. Sie stehen in test.sql: Jede Prüfabfrage gibt t aus, wenn sie besteht.

Häufige Fehler

Eine Spalte, die weder gruppiert noch aggregiert ist

SELECT genre, title, count(*)
FROM books
GROUP BY genre;

Was psql ausgibt

ERROR:  column "books.title" must appear in the GROUP BY clause or be used in an aggregate function

Warum, und die Lösung

Die Gruppe sci-fi enthält drei Titel, und ihre eine Ergebniszeile hat Platz für einen Wert. PostgreSQL wählt keinen für Sie aus. Gruppieren Sie entweder auch nach title (dann ist jedes Buch eine eigene Gruppe, und alle Zählwerte sind 1), oder setzen Sie title in ein Aggregat: min(title) für einen davon, count(title) für ihre Anzahl.

Text addieren

SELECT genre, sum(title)
FROM books
GROUP BY genre;

Was psql ausgibt

ERROR:  function sum(text) does not exist

Warum, und die Lösung

sum und avg gibt es nur für Zahlentypen (und Intervalle), also keine Summe einer Textspalte. Um die Titel zu zählen, schreiben Sie count(title); für den alphabetisch ersten Titel min(title). Eine Typumwandlung hilft hier nicht: Die Titel sind keine Zahlen.

Ein Aggregat in einem Aggregat

SELECT max(count(*))
FROM loans
GROUP BY member_id;

Was psql ausgibt

ERROR:  aggregate function calls cannot be nested

Warum, und die Lösung

Gesucht war die höchste Zahl von Ausleihen je Mitglied, aber das Argument eines Aggregats darf kein weiteres Aggregat enthalten. Berechnen Sie die Zahlen je Gruppe und sortieren Sie sie: SELECT member_id, count(*) AS loans FROM loans GROUP BY member_id ORDER BY loans DESC LIMIT 1. Hier liegen zwei Mitglieder mit 2 gleichauf, also ergänzen Sie einen zweiten Sortierschlüssel, der entscheidet, wer vorn steht.

PostgreSQL im Browser: PGlite 0.5.8 (PostgreSQL 18.3), Apache-2.0 und PostgreSQL License. Lizenz und Quellcode

Abschlussquiz

5 Fragen, ohne Hinweise. Ab 80 % ist die Lektion abgeschlossen.

Erledigen Sie zuerst alle Aufgaben oben, um das Abschlussquiz freizuschalten.

Problem melden

Etwas ist falsch oder unklar? Beschreiben Sie es kurz, dann wird es geprüft und korrigiert.

#

Mindestens 20 Zeichen.

Nur, wenn Sie eine Antwort wünschen.

Kernideen

Ein Aggregat macht aus vielen Zeilen einen Wert

count(*) zählt Zeilen. count(returned_on) zählt nur die Zeilen, in denen returned_on nicht NULL ist, und count(DISTINCT member_id) zählt verschiedene Werte: fünf Ausleihen, zwei zurückgegeben, drei Ausleihende. Auch sum, avg, min und max überspringen NULL. Achten Sie auf die Ergebnistypen: count(*) und die Summe einer integer-Spalte sind bigint, der Durchschnitt einer integer-Spalte ist numeric mit vielen Nachkommastellen, 1.5000000000000000; round(avg(copies), 2) zeigt 1.50. min und max funktionieren mit Zahlen, Text und Datumswerten, sum und avg brauchen Zahlen: sum(title) scheitert mit function sum(text) does not exist.

GROUP BY bildet eine Zeile je Gruppe

GROUP BY genre fasst die Zeilen mit demselben Genre zu einer Gruppe zusammen, und die Abfrage liefert eine Zeile je Gruppe; jedes Aggregat wird über die Zeilen dieser Gruppe berechnet: vier Genres, vier Zeilen. Mit GROUP BY genre, copies ist jede Gruppe ein eigenes Wertepaar, sci-fi zerfällt also in zwei Zeilen. Zeilen, deren Gruppierungsspalte NULL ist, bilden eine eigene Gruppe. Nach dem Gruppieren muss jede Spalte der SELECT-Liste gruppiert sein oder in einem Aggregat stehen: Ein Genre hat drei Titel, und PostgreSQL wählt keinen davon aus. Joins kommen zuerst: Verbinden Sie loans mit books, dann zählt GROUP BY b.genre die Ausleihen je Genre.

Keine Zeilen und die Reihenfolge der Gruppen

Ohne GROUP BY liefert eine Abfrage mit Aggregat genau eine Zeile, auch wenn WHERE gar keine Zeile übrig lässt: count(*) ist dann 0, sum, avg, min und max sind NULL, nicht 0. Schreiben Sie coalesce(sum(copies), 0), wenn ein Bericht eine 0 braucht. Gruppen kommen in keiner zugesicherten Reihenfolge, also ergänzen Sie ORDER BY. Sie können nach einem Aggregat sortieren, ORDER BY count(*) DESC, oder nach seinem Alias, ORDER BY books DESC, mit einem zweiten Schlüssel wie genre, damit Gleichstände fest geordnet sind. Aggregate lassen sich nicht schachteln: max(count(*)) scheitert. Sortieren Sie stattdessen die Zahlen und behalten Sie mit LIMIT 1 die erste Zeile.

Quellen

Zuletzt geprüft am 30. September 2026