SUM, COUNT, MIN, MAX, AVG
Hinweise zu den Übungen
Das sind viele Übungen. Man kann nur die machen, die mit einem Stern markiert sind. (Und die schnelleren Schüler/innen machen die anderen auch noch.)
Kowalsky!! Sie hat mir so einen Inder mit IQ 148 geschickt! Der will irgendwelches Zeug von mir, und ich habe keine Plan, was -
Beruhige dich doch. Das kriegst du hin. Was will er denn, der Wunderknabe?
Ich soll die Datenbank irgendwie aufbereiten, hab's nicht ganz verstanden. Er hat was von durchschnittlichem Einkommen oder Anzahl der Bewohner geredet - ich weiß gar nicht, ob das überhaupt geht!
Klar geht das. Nichts leichter als das. Lass mal sehen … Hier habe ich noch Unterlagen, die ich mal für einen Praktikanten zusammengestellt habe.
Aggregatfunktionen in SQL
Angenommen, du hast folgende Liste:
Jetzt möchtest du das durchschnittliche Einkommen berechnen. Oder du möchtest wissen, welche von den Personen die größte ist. Das machst du mit den sogenannten Aggregatfunktionen ("aggregieren" heißt so viel wie "zusammenfassen"). Für dich sind die folgenden wichtig:
- COUNT (Zählt, wie viele Zeilen dein Ergebnis hat)
- SUM (Summe aller Werte eines Attributs)
- AVG (Durchschnitt aller Werte eines Attributs)
- MIN (Kleinster Wert einer Spalte)
- MAX (Größter Wert einer Spalte)
Das kennst du schon von Excel. Beispiel: Du willst den höchsten Kontostand in der Datenbank. Also schreibst du
select max(p.kontostand)
from personen p;
Beachte, dass alle Ergebnisse auf EINE ZEILE eingedampft werden. Deshalb funktioniert
select p.nachname, max(p.kontostand)
from personen p;
NICHT! Eigentlich stehen im Feld p.nachname alle Personen, die für die Berechnung des Maximums herangezogen wurden. Dazu mehr später.
Du kannst auch mehrere Aggregate durch Komma trennen:
select min(p.kontostand), max(p.kontostand)
from personen p;
COUNT zählt, wie viele Ergebnisse du hast. Wenn du wissen willst, wie viele Personen mit schwarzen Haaren es in der Datenbank gibt, schreibst du
select count(*)
from personen p
where p.haarfarbe = 'schwarz';
Bei COUNT ist das einfachste für Sie, in die Klammer IMMER den Stern zu schreiben (der Stern steht für »alles«). Sie können auch bspw. den Primärschlüssel verwenden, das macht in unseren Anwendungskontexten aber nie einen Unterschied:
select count(p.pNr) from personen p;
Verwendung eines Alias
Oft empfiehlt es sich, Abkürzungen (= "Alias") zu verwenden. Das machen wir schon immer, wenn wir den Tabellen Abkürzungen geben:
select * from personen p
Wenn wir Leerzeichen benutzen wollen, müssen wir das Alias in Backticks schreiben - dann aber unbedingt mit AS
:
select max(p.kontostand) AS `Höchster Kontostand`
from personen p;
Die Backticks kriegst du so:
Es funktionieren zwar auch auch doppelte Anführungszeichen oder in SQLite eckige Klammern, aber DU verwendest IMMER NUR Backticks und zwar immer mit AS. Falls du mal eine MySQL-Datenbank bedienen musst - dort funktionieren nur Backticks zuverlässig!
TIPP: Am besten tippen Sie immer direkt zweimal den Backtick und schreiben die Bezeichnung für das Alias zwischenrein.
Aggregatfunktionen und vertikale Einschränkung
Wie gesagt: Aggregatfunktionen dampfen alle Ergebniszeilen auf EINE ein - auch wenn du komplexere Abfragen hast wie
select avg(p.kontostand) AS `ø Kontostand
der Wasabisucher` from personen p, googleSuchanfragen g where p.pNr = g.FK_pNr and (g.suchbegriff like '%wasabi%' or g.suchbegriff like '%rettich%');
Das berechnet dir den durchschnittlichen Kontostand aller Personen, in deren Suchanfragen die Zeichenkette "wasabi" oder "rettich" vorkamen.
Runden
Runden machst du mit ROUND - in Klammern steht der Wert, dann nach einem Komma die Anzahl der Nachkommastellen:
select round(avg(p.kontostand), 2)
AS `Kontostand gerundet`
from personen p;
Uff - aber ich glaube, das sieht nach mehr aus, als es ist. Mal sehen, was der Intelligenzbolzen hier aufgeschrieben hat:
- * How many citizens live in Maulwurfstadt? As headline use "Number of citizens" (= alias), please.
- * What's the average income of all citizens? Please round to 2 decimals.
- * Bank balance of all citizens: highest, lowest, average.
- What's the total monthly income of all citizens, and what's their average monthly income? Use "Total income", "Average income" as an alias). Please round the average income to zero decimals.
- * How many citizens with a bank balance of more than 10.000 Euros do live in Maulwurfstadt? Alias: "Rich people"
- * What's the total bank balance of all citizens?
- What are the most expensive and the cheapest goods in "waren"? Please use aliases ("Most expensive", "Cheapest").
- How many stays ("aufenthalte") on Christmas Eve 2033 can be found in the database?
- * How many "tatbestande" can be found in the table "straftaten"? As alias use "Number of tatbestande"
- * In the straftaten-table - what's the highest and the lowest degree of penalty?
- * I need the average monthly income of all children in Maulwurfstadt (today's date is 29.06.2034 - children are all citizens younger than 13 years old). Please round to 2 decimals. Alias: "Average income of children"
- How many teenagers (age between 12 and 18 years referring to 29.06.2034) have no monthly income, how many have a monthly income? As headlines use "Teenagers with monthly income", "Teenager without monthly income". Probably you should use two different queries ...
- How many unmarried women aged under 20 can be found in the database (referring to 29.06.2034)?
So. Geschafft.
*pock, *pock
Ah - Mr. Kapoor! Here is the data you have requested! (hands it over to him)
Thank you, Mr. Smith. I will review it in my office and get back to you in about 30 minutes. (ab)
In seinem Office??? Wohnt der jetzt hier?!
Reg dich nicht auf. Ist doch egal, Hauptsache, er ist weg.
Stimmt auch. Ich bin froh - aber …Schau mal da draußen!
Die Maulwurfstadt
Ein streng geheimes Projekt: In einem kleinen Dorf testet das Cyber-HQ eine neue Überwachungssoftware …