modul:m290_guko:learningunits:lu10:theorie:a_einfuehrung

Dies ist eine alte Version des Dokuments!

LU10: Aggregatfunktionen (MySQL) – mit Beispielen aus «Einbrüche Kanton Zürich»

Datensatz: Anzahl Einbrüche nach Gemeinden und Stadtkreisen des Kantons Zürich

Um die Code-Beispiele nachvollziehen zu können, brauchen Sie die Datenbank zh_einbrueche, die Sie hier als .zip-File downloaden können: Zip-File mit Datenbank (sql-File) zu Einbrüchen im Kanton Zürich. 1)

1. Aggregatfunktionen – Theorie & Motivation

Was sind Aggregatfunktionen?

Aggregatfunktionen berechnen einen zusammengefassten Wert über mehrere Zeilen (Records). Typische Aufgaben:

  • Zählen (Wie viele Einbrüche?)
  • Summieren (Wie viele Fälle insgesamt?)
  • Durchschnitt (Ø‑Häufigkeitszahl pro Jahr)
  • Minimum/Maximum (geringste/höchste Zahl pro Gemeinde)

Wozu braucht man das?

  • Datenanalyse & Reporting (z.B. Lageberichte für Polizei/Medien)
  • Business Intelligence (Trends, Hotspots je Gemeinde/Jahr)
  • Produkt-/Web‑Integration (APIs liefern Rohdaten → Aggregation im Backend/SQL spart App‑Logik)
  • Entscheidungsgrundlagen (Ressourcen planen, Präventionsmassnahmen priorisieren)

1.1 Wichtigste Aggregatfunktionen

Funktion Zweck NULLs
COUNT(*) Zeilen zählen zählt alle Zeilen
COUNT(spalte) Nicht‑NULL‑Werte zählen ignoriert NULL
SUM(spalte) Summe ignoriert NULL
AVG(spalte) Durchschnitt ignoriert NULL
MIN(spalte) Minimum ignoriert NULL
MAX(spalte) Maximum ignoriert NULL

Syntax 

SELECT AGGREGATFUNKTION(ausdruck) AS alias
FROM zh_einbrueche.einbrueche;

1.2 Einstiegsbeispiele (ohne GROUP BY)

Anzahl unterschiedlicher Gemeinden im Datensatz

SELECT COUNT(DISTINCT gemeindename) AS anzahl_gemeinden
FROM zh_einbrueche.einbrueche;

Ergebnis (Auszug)

anzahl_gemeinden
161

Höchster Einzelwert pro Zeile (Totalfälle)

SELECT MAX(straftaten_total) AS max_faelle_in_einer_zeile
FROM zh_einbrueche.einbrueche;

Ergebnis (Auszug)

max_faelle_in_einer_zeile
1060

2. GROUP BY – Daten zu Gruppen zusammenfassen

Warum ist GROUP BY wichtig? Ohne GROUP BY erhalten Sie einen Aggregatwert über alle Zeilen. Mit GROUP BY erhalten Sie je Gruppe eine Zeile (z.B. pro Jahr, pro Gemeinde oder pro Kombination aus Jahr+Gemeinde). Man kann so nach einzelnen Einträgen gruppieren.

Was darf in SELECT stehen?

Nur Aggregatfunktionen (z.B. SUM(…), AVG(…)) oder Spalten, die im GROUP BY aufgeführt sind.

Gültig: SELECT ausgangsjahr, SUM(straftaten_total) FROM zh_einbrueche.einbrueche GROUP BY ausgangsjahr;

Ungültig (fehlendes GROUP BY für gemeindename): SELECT gemeindename, SUM(straftaten_total) FROM zh_einbrueche.einbrueche; – führt zu Fehler/Zufallswerten

WHERE vs. HAVING

WHERE filtert einzelne Zeilen vor dem Gruppieren. HAVING filtert fertige Gruppen nach dem Gruppieren.

Beispiel: Nur Jahre ab 2018 berücksichtigen (WHERE), und nur Gruppen mit Total > 1'000 zeigen (HAVING) 

SELECT ausgangsjahr, SUM(straftaten_total) AS total_faelle 
FROM zh_einbrueche.einbrueche
WHERE ausgangsjahr >= 2018 -- Zeilenfilter
GROUP BY ausgangsjahr
HAVING SUM(straftaten_total) > 15000; -- Gruppenfilter

Ergebnis: In den Jahren 2018, 2019 und 2024 haben über 15000 Einbrüche total stattgefunden.

Alias in GROUP BY

MySQL: Alias kann verwendet werden. Für Portabilität (z.B. andere SQL‑Dialekte): Ausdruck wiederholen.

2.1 Beispiele: Gruppieren nach einem Kriterium

a) Total Einbrüche pro Jahr 

SELECT ausgangsjahr,
SUM(straftaten_total) AS total_faelle
FROM   zh_einbrueche.einbrueche
GROUP  BY ausgangsjahr
ORDER  BY ausgangsjahr;

Ergebnis (Auszug)

ausgangsjahr total_faelle
2019 5080
2020 4525
2021 4789

b) Anzahl erfasste Gemeinden pro Jahr 

SELECT ausgangsjahr,
COUNT(DISTINCT gemeindename) AS anzahl_gemeinden
FROM   zh_einbrueche.einbrueche
GROUP  BY ausgangsjahr
ORDER  BY ausgangsjahr;

Ergebnis (Auszug)

ausgangsjahr anzahl_gemeinden
2019 171
2020 171
2021 171

2.2 Beispiele: Gruppieren nach mehreren Kriterien

Total pro Jahr und Tatbestand (z.B. Einbruch, Einschleichen) 

SELECT ausgangsjahr,
tatbestand,
SUM(straftaten_total) AS total_faelle
FROM   zh_einbrueche.einbrueche
GROUP  BY ausgangsjahr, tatbestand
ORDER  BY ausgangsjahr, tatbestand;

Ergebnis (Auszug)

ausgangsjahr tatbestand total_faelle
2020 Einbruch 3100
2020 Einschleichen 1425
2021 Einbruch 3250

3. HAVING – Gruppen gezielt filtern

WHERE kann keine Aggregatfunktionen enthalten. Wenn Sie Ergebnisgruppen (nach GROUP BY) filtern wollen, verwenden Sie HAVING.

Jahre mit über 5'000 Fällen insgesamt 

SELECT ausgangsjahr,
SUM(straftaten_total) AS total_faelle
FROM   zh_einbrueche.einbrueche
GROUP  BY ausgangsjahr
HAVING SUM(straftaten_total) > 5000
ORDER  BY total_faelle DESC;

Ergebnis (Auszug)

ausgangsjahr total_faelle
2013 6240
2012 6115

Abarbeitungsreihenfolge (vereinfacht) FROM → WHERE → GROUP BY → Aggregatfunktionen → HAVING → SELECT → ORDER BY.

4. Häufige Stolpersteine (und wie man sie vermeidet)

* SELECT enthält ungegruppte Spalten → Fehlermeldung oder Zufallswerte. 

→ Nur Spalten in GROUP BY **oder** in Aggregaten verwenden.

* Division durch 0 bei Anteilen/Quoten. 

→ NULLIF(denominator,0) verwenden.

* MySQL‑Spezialität: Alias in GROUP BY ist erlaubt, aber nicht portabel. 

→ Ausdruck wiederholen, wenn Portabilität wichtig ist.

* NULLs: COUNT(spalte) ignoriert NULL → bewusste Spaltenwahl!

LU10: Aggregatfunktionen (MySQL) – mit Beispielen aus «Einbrüche Kanton Zürich»

Datensatz: *Anzahl Einbrüche nach Gemeinden und Stadtkreisen des Kantons Zürich*

Tabellen- & Feldübersicht (vereinfacht) Datenbank: zh_einbrueche • Tabelle: einbrueche

Feld Typ (MySQL) Pflicht Bedeutung
id BIGINT UNSIGNED ja (PK) künstlicher Primärschlüssel
ausgangsjahr YEAR ja Berichtsjahr der PKS (nicht Tatzeitpunkt)
gemeinde_bfs_nr INT ja BFS-Nummer der Gemeinde
gemeindename VARCHAR(100) ja Gemeindename
stadtkreis_bfs_nr INT nein Nur Stadt Zürich: BFS-Nr. des Stadtkreises
stadtkreis_name VARCHAR(100) nein Nur Stadt Zürich: Name des Stadtkreises
gesetz_nummer DECIMAL(6,1) ja Gesetzesnummer des Tatbestands
gesetz_abk VARCHAR(16) ja Gesetzesabkürzung (z.B. StGB)
tatbestand VARCHAR(100) ja z.B. Einbruch, Einschleichen
straftaten_total INT ja Vollendet + Versucht
straftaten_vollendet INT ja Vollendete Taten
straftaten_versucht INT ja Versuch
einwohner INT ja Bevölkerungszahl (Ende Vorjahr)
haeufigkeitszahl DECIMAL(10,2) ja Taten pro 1'000 Einwohner

1. Aggregatfunktionen – Theorie & Motivation

Was sind Aggregatfunktionen? Aggregatfunktionen berechnen einen zusammengefassten Wert über mehrere Zeilen (Records). Typische Aufgaben:

* Zählen (Wie viele Einbrüche?) * Summieren (Wie viele Fälle insgesamt?) * Durchschnitt (Ø‑Häufigkeitszahl pro Jahr) * Minimum/Maximum (geringste/höchste Zahl pro Gemeinde)

Wozu braucht man das?

* Datenanalyse & Reporting (z.B. Lageberichte für Polizei/Medien) * Business Intelligence (Trends, Hotspots je Gemeinde/Jahr) * Produkt-/Web‑Integration (APIs liefern Rohdaten → Aggregation im Backend/SQL spart App‑Logik) * Entscheidungsgrundlagen (Ressourcen planen, Präventionsmassnahmen priorisieren)

1.1 Wichtigste Funktionen (NULL-Verhalten)

Funktion Zweck NULLs Beispiel
COUNT(*) Zeilen zählen zählt alle Zeilen Anzahl Zeilen im Jahr
COUNT(spalte) Nicht‑NULL‑Werte zählen ignoriert NULL z.B. vorhandene Werte in einer Spalte
SUM(spalte) Summe ignoriert NULL Total aller straftaten_total
AVG(spalte) Durchschnitt ignoriert NULL Ø haeufigkeitszahl pro Jahr
MIN(spalte) Minimum ignoriert NULL tiefste straftaten_total pro Gemeinde
MAX(spalte) Maximum ignoriert NULL höchste straftaten_total pro Gemeinde

Syntax 

SELECT AGGREGATFUNKTION([DISTINCT|ALL] ausdruck) AS alias
FROM zh_einbrueche.einbrueche;

MySQL‑Standard ist ALL (= Duplikate werden mitgerechnet). DISTINCT rechnet nur mit einzigartigen Werten.

1.2 Einstiegsbeispiele (ohne GROUP BY)

Anzahl unterschiedlicher Gemeinden im Datensatz 

SELECT COUNT(DISTINCT gemeindename) AS anzahl_gemeinden
FROM zh_einbrueche.einbrueche;

Ergebnis (Auszug)

anzahl_gemeinden
171

Höchster Einzelwert pro Zeile (Totalfälle) 

SELECT MAX(straftaten_total) AS max_faelle_in_einer_zeile
FROM zh_einbrueche.einbrueche;

Ergebnis (Auszug)

max_faelle_in_einer_zeile
420

2. GROUP BY – Daten zu Gruppen zusammenfassen

Warum ist GROUP BY wichtig? Ohne GROUP BY erhalten Sie einen Aggregatwert über alle Zeilen. Mit GROUP BY erhalten Sie je Gruppe eine Zeile (z.B. pro Jahr, pro Gemeinde oder pro Kombination aus Jahr+Gemeinde). Das ist zentral für Trends, Rankings und Vergleiche.

Regeln (MySQL‑kompatibel, Standard‑konform formuliert):

* In SELECT dürfen neben Aggregaten nur Spalten stehen, die auch im GROUP BY vorkommen. * WHERE filtert Zeilen vor dem Gruppieren. HAVING filtert Gruppen nach dem Gruppieren. * MySQL erlaubt teils Aliasse in GROUP BY (z.B. GROUP BY jahr nach SELECT YEAR(…) AS jahr). Für bessere Portabilität: Ausdruck wiederholen.

2.1 Beispiele: Gruppieren nach einem Kriterium

a) Total Einbrüche pro Jahr 

SELECT ausgangsjahr,
SUM(straftaten_total) AS total_faelle
FROM   zh_einbrueche.einbrueche
GROUP  BY ausgangsjahr
ORDER  BY ausgangsjahr;

Ergebnis (Auszug)

ausgangsjahr total_faelle
2019 5080
2020 4525
2021 4789

b) Anzahl erfasste Gemeinden pro Jahr 

SELECT ausgangsjahr,
COUNT(DISTINCT gemeindename) AS anzahl_gemeinden
FROM   zh_einbrueche.einbrueche
GROUP  BY ausgangsjahr
ORDER  BY ausgangsjahr;

Ergebnis (Auszug)

ausgangsjahr anzahl_gemeinden
2019 171
2020 171
2021 171

2.2 Beispiele: Gruppieren nach mehreren Kriterien

Total pro Jahr und Tatbestand (z.B. Einbruch, Einschleichen) 

SELECT ausgangsjahr,
tatbestand,
SUM(straftaten_total) AS total_faelle
FROM   zh_einbrueche.einbrueche
GROUP  BY ausgangsjahr, tatbestand
ORDER  BY ausgangsjahr, tatbestand;

Ergebnis (Auszug)

ausgangsjahr tatbestand total_faelle
2020 Einbruch 3100
2020 Einschleichen 1425
2021 Einbruch 3250

3. HAVING – Gruppen gezielt filtern

WHERE kann keine Aggregatfunktionen enthalten. Wenn Sie Ergebnisgruppen (nach GROUP BY) filtern wollen, verwenden Sie HAVING.

Jahre mit über 5'000 Fällen insgesamt 

SELECT ausgangsjahr,
SUM(straftaten_total) AS total_faelle
FROM   zh_einbrueche.einbrueche
GROUP  BY ausgangsjahr
HAVING SUM(straftaten_total) > 5000
ORDER  BY total_faelle DESC;

Ergebnis (Auszug)

ausgangsjahr total_faelle
2013 6240
2012 6115

Abarbeitungsreihenfolge (vereinfacht) FROM → WHERE → GROUP BY → Aggregatfunktionen → HAVING → SELECT → ORDER BY.

4. Häufige Stolpersteine (und wie man sie vermeidet)

* SELECT enthält ungegruppte Spalten → Fehlermeldung oder Zufallswerte. 

→ Nur Spalten in GROUP BY **oder** in Aggregaten verwenden.

* Division durch 0 bei Anteilen/Quoten. 

→ NULLIF(denominator,0) verwenden.

* MySQL‑Spezialität: Alias in GROUP BY ist erlaubt, aber nicht portabel. 

→ Ausdruck wiederholen, wenn Portabilität wichtig ist.

* NULLs: COUNT(spalte) ignoriert NULL → bewusste Spaltenwahl!

1)
Datenquelle: Kantonspolizei des Kantons Zürich
  • modul/m290_guko/learningunits/lu10/theorie/a_einfuehrung.1762363985.txt.gz
  • Zuletzt geändert: 2025/11/05 18:33
  • von gkoch