Ermitteln, wieviele Datensätze in der Datenbank sind
Die Anzahl der unterschiedlichen Kunden in den einzelnen Orten ermitteln
Die Funktion sum
Die Funktion sum auf mehrere Gruppen anwenden
Mehere Gruppenfunktionen in einem Statement
Die Funktion avg (average): Den Durchschnitt ermitteln
Einen Alias für einen Spaltennamen anlegen
Maximum, Minimum berechnen: Die Funktionen max(), min()
max(),min() angewendet auf mehrere Gruppen
group by in Verbindung mit having
Die Stadt mit den meisten Kunden ermitteln

Ermitteln, wieviele Datensätze in der Datenbank sind

nach oben

Es wird von der Tabelle ausgegangen, die im Kapitel Daten aus einem Flatfile in Tabellen importieren eingerichtet wurde. Wer mit den Grundlagen von mysql nicht vertraut ist, sollte sich das in Kapitel mysql basics Beschriebene vergegenwärtigen. Hier nochmal die Struktur, die allen Beispielen in diesem Kapitel zugrunde liegt.

Um zu ermitteln, wieviele Datensätze in der Datenbank sind, verwenden wir die Funktion count(). In ihrer einfachsten Variante sieht sie so aus.
select count(*) from testerone;

Das liefert dann als Ergebnis 10. Wir zeigen nochmal den Ablauf in der Dox Box (Eingabeaufforderung).

mysql> select count(*) from testerone;

	count(*)
	10

1 row in set (0.27 sec)
mysql>

Ermitteln, wieviele verschiedene Orte in der Datenbank sind
Wenn wir ermitteln wollen, wieviele verschiedene Orte in der Datenbank sind, können wir so etwas machen.
select count(distinct(Ort)) from testerone;

Wir erhalten

mysql> select count(distinct(Ort)) from testerone;

	count(distinct(Ort))
	6

1 row in set (0.05 sec)
mysql>

Machen wir uns das klar. Die Funktion distinct() ermittelt die Anzahl der unterschiedlichen Orte.

mysql> select distinct(Ort) from testerone;

	Ort
	Berlin
	München
	Freiburg
	Bremen
	Düsseldorf
	Coburg

6 rows in set (0.00 sec)

Ermitteln wir hiervon wieder die Anzahl, haben wir die Anzahl der unterschiedlichen Orte.

Die Anzahl der unterschiedlichen Kunden in den einzelnen Orten ermitteln

nach oben

Die meisten kennen ebay und Konsorten, also die ganzen Anzeigenmärkte. Bei diesen erscheinen immer so wunderhübsch die Anzahl der Einträge in einer bestimmten Kategorie (alle Autos in Hamburg, alle Angebote des Users XY etc. etc.). In unserem Beispiel könnte man sich zum Beispiel dafür interessieren, wieviele Kunden in den einzelnen Städten sind. Bis zu einer bestimmten Anzahl von Datensätzen, kann man das mit einem einfachen select statement machen.

mysql> select count(distinct(Name)),Ort from testerone group by Ort;

	count(distinct(Name))		Ort
	2		Berlin
	1		Bremen
	1		Coburg
	1		Düsseldorf
	4		Freiburg
	1		München

6 rows in set (0.05 sec)
mysql>

Die Funktion sum

nach oben

Wenn wir wissen wollen, was die einzelne Kategorie (business,consumer) an Umsatz bringt, können wir sowas machen

mysql> select Kategorie,sum(Umsatz) from testerone group by Kategorie;

	Kategorie		sum(Umsatz)
	business		98000
	consumer		176000

2 rows in set (0.00 sec)
mysql>

Die Funktion sum auf mehrere Gruppen anwenden

nach oben

Wenn wir wissen wollen, was die einzelne Kategorie in den einzelnen Orten an Umsatz bringt, machen wir sowas.

mysql> select Kategorie,Ort,sum(Umsatz) from testerone group by
-> Kategorie,Ort;

	Kategorie		Ort		sum(Umsatz)
	business		Berlin		30000
	business		Freiburg		28000
	business		München		40000
	consumer		Berlin		5000
	consumer		Bremen		6000
	consumer		Coburg		90000
	consumer		Düsseldorf		70000
	consumer		Freiburg		5000

8 rows in set (0.00 sec)
mysql>

Mehere Gruppenfunktionen in einem Statement

nach oben

Wenn wir wissen wollen, was die einzelne Kategorie in den einzelnen Orten an Umsatz bringt und wieviele unterschiedliche Kunden wir in den verschiedenen Kategorien haben, machen wir sowas.

mysql> select Kategorie,Ort, sum(Umsatz),count(distinct(Name))
-> from testerone group by Kategorie,Ort;

	Kategorie		Ort		sum(Umsatz)		count(distinct(Name))
	business		Berlin		30000		1
	business		Freiburg		28000		3
	business		München		40000		1
	consumer		Berlin		5000		1
	consumer		Bremen		6000		1
	consumer		Coburg		90000		1
	consumer		Düsseldorf		70000		1
	consumer		Freiburg		5000		1

8 rows in set (0.00 sec)
mysql>

Die Funktion avg (average): Den Durchschnitt ermitteln

nach oben

Wenn Sie wissen wollen, ob die business Kunden oder die Consumer Kunden durchschnittlich mehr Umsatz bringen, können sie sowas machen.

mysql> select Kategorie,AVG(Umsatz) from testerone group by Kategorie;

	Kategorie		AVG(Umsatz)
	business		19600.0000
	consumer		35200.0000

2 rows in set (0.00 sec)
mysql>

Einen Alias für einen Spaltennamen anlegen

nach oben

Der Spaltenname AVG(Umsatz) ist hierbei unschön, wer will kann das ändern, in irgendetwas Aussagekräftiges.

mysql> select Kategorie, AVG(Umsatz) as 'durchschschnittlicher Umsatz pro Kunde'
-> from testerone group by Kategorie;

	Kategorie		durchschschnittlicher Umsatz pro Kunde
	business		19600.0000
	consumer		35200.0000

2 rows in set (0.06 sec)
mysql>

Maximum, Minimum berechnen: Die Funktionen max(), min()

nach oben

Wenn man wissen will was der höchste und der geringste Umsatz ist in jeder Stadt ist, kann man sowas machen. Bei der Gelegenheit macht es noch Sinn, die Spalten aussagekräftig zu benennen.

mysql> select Ort, min(Umsatz) as 'geringster Umsatz', max(Umsatz)
-> as 'höchster Umsatz' from testerone group by Ort;

	Ort		geringster Umsatz		höchster Umsatz
	Berlin		5000		30000
	Bremen		6000		6000
	Coburg		90000		90000
	Düsseldorf		70000		70000
	Freiburg		4000		20000
	München		40000		40000

6 rows in set (0.05 sec)
mysql>

max(),min() angewendet auf mehrere Gruppen

nach oben

Wenn man es noch genauer wissen will, also was der geringste Umsatz, der höchste Umsatz in jeder Stadt ist, aber noch nach Kategorie diskriminieren will, kann man sowas machen.

mysql> select Ort,min(Umsatz) as 'geringster Umsatz', max(Umsatz) as
-> 'höchster Umsatz' from testerone group by Ort,Kategorie;

	Ort		geringster Umsatz		höchster Umsatz
	Berlin		30000		30000
	Berlin		5000		5000
	Bremen		6000		6000
	Coburg		90000		90000
	Düsseldorf		70000		70000
	Freiburg		4000		20000
	Freiburg		5000		5000
	München		40000		40000

8 rows in set (0.00 sec)
mysql>

Leider ist das nicht besonders aussagekräftig, weil wir nicht wissen, wer zu wem gehört. Wir sollten uns also die Kategorie mit ausdrucken lassen.

mysql> select Ort, min(Umsatz) as 'geringster Umsatz', max(Umsatz) as
-> 'höchster Umsatz', Kategorie from testerone group by Ort,Kategorie;

	Ort		geringster Umsatz		höchster Umsatz		Kategorie
	Berlin		30000		30000		business
	Berlin		5000		5000		consumer
	Bremen		6000		6000		consumer
	Coburg		90000		90000		consumer
	Düsseldorf		70000		70000		consumer
	Freiburg		4000		20000		business
	Freiburg		5000		5000		consumer
	München		40000		40000		business

8 rows in set (0.00 sec)

group by wird nur gebraucht in Verbindung mit min,max,sum,avg da in diesem Falle unklar ist auf welche Teilmenge die Funktionen überhaupt angewendet werden sollen. group by bildet also erstmal Teilmengen, Freiburg/business,Freiburg/consumer,Berlin/business,Berlin/consumer etc. etc. und wendet die Funktionen dann auf diese Teilmengen an. Es gibt nur eine Situation, bei der min,max,count,avg auch ohne group by verwendet werden kann, nämlich dann, wenn nur der eine Wert, auf den die Funktion angewendet werden soll, gezeigt werden soll.

mysql> select min(Umsatz) from testerone;

	min(Umsatz)
	4000

1 row in set (0.00 sec)
mysql>

Das allerdings geht schon schief.

mysql> select min(Umsatz),Name from testerone;
ERROR 1140: Mixing of GROUP columns (MIN(),MAX(),COUNT()...) with no GROUP colum
ns is illegal if there is no GROUP BY clause
mysql>

group by in Verbindung mit having

nach oben

Wenn wir uns nur Städte anzeigen wollen, in denen mehr als 50000 Umsatz generiert worden ist, brauchen wir having.

mysql> select sum(Umsatz),Ort from testerone group by Ort
-> having sum(Umsatz)>=50000;

	sum(Umsatz)		Ort
	90000		Coburg
	70000		Düsseldorf

2 rows in set (0.05 sec)
mysql>

having braucht man also, weil where nicht mit Funktionen umgehen kann. Etwas der Art "select sum(Umsatz), Ort from testerone group by Ort where sum(Umsatz)>=5000;" funktionniert also nicht. Wir können noch kurz überprüfen, ob das Ergebnis oben richtig ist, indem wir nochmal 'mit der Hand' kontrollieren.

mysql> select Ort,sum(Umsatz) from testerone group by Ort;

	Ort		sum(Umsatz)
	Berlin		35000
	Bremen		6000
	Coburg		90000
	Düsseldorf		70000
	Freiburg		33000
	München		40000

6 rows in set (0.00 sec)
mysql>

Wir sehen also, das obige Ergebnis ist richtig. Wenn wir wissen wollen, wieviele Orte es gibt, wo wir mehr als zwei Kunden haben, können wir sowas machen.

mysql> select count(Name) as 'Anzahl Kunden', Ort from testerone
-> group by Ort having count(Name)>2;

	Anzahl Kunden		Ort
	4		Freiburg

1 row in set (0.11 sec)
mysql>

Um zu überprüfen ob das stimmt, schauen wir nochmal 'mit der Hand' nach.

mysql> select count(Name),Ort from testerone group by Ort;

	count(Name)		Ort
	2		Berlin
	1		Bremen
	1		Coburg
	1		Düsseldorf
	4		Freiburg
	1		München

6 rows in set (0.06 sec)
mysql>

Wir sehen, nur Freiburg hat mehr als 2 (2 ist ja bekanntlich nicht mehr als 2). Wenn wir nur die Orte haben wollen, wo wir genau 2 oder mehr business Kunden haben, müssen wir so was machen.

mysql> select count(Name) as 'Anzahl Kunden',Ort,Kategorie from testerone
-> group by Ort,Kategorie having count(Name)>2;

	Anzahl Kunden		Ort		Kategorie
	3		Freiburg		business

1 row in set (0.00 sec)
mysql>

Wieder erhalten wir nur Freiburg, weil wir nur in Freiburg mehr als zwei business Kunden haben. Das können wir jetzt wieder mit 'der Hand' kontrollieren.

mysql> select count(Name) as 'Anzahl Kunden',Ort,Kategorie from testerone
-> group by Ort,Kategorie;

	Anzahl Kunden		Ort		Kategorie
	1		Berlin		business
	1		Berlin		consumer
	1		Bremen		consumer
	1		Coburg		consumer
	1		Düsseldorf		consumer
	3		Freiburg		business
	1		Freiburg		consumer
	1		München		business

8 rows in set (0.00 sec)
mysql>

Die Stadt mit den meisten Kunden ermitteln

nach oben

Wie man sofort sieht, ist das Ergebnis oben richtig. Wenn wir die Stadt ermitteln wollen, wo wir die meisten business Kunden haben, können wir sowas machen.

mysql> select count(Name) as 'Anzahl der Kunden',
-> Ort as 'Ort mit den meisten Kunden' from testerone
-> group by (Ort) order by 'Anzahl der Kunden' desc limit 1;

	Anzahl der Kunden		Ort mit den meisten Kunden
	4		Freiburg

1 row in set (0.06 sec)
mysql>

Das heisst, wir bilden Gruppen mit den Orten, holen uns aus diesen Gruppen die Anzahl der Kunden und sortieren diese nach der Anzahl in absteigender Reihenfolge (die Gruppe mit den meisten Kunden zuerst) und beschränken die Anzahl der aufgelisteten Datensätze auf 1. Wir müssen in diesem Fall mit dem Alias für den Spaltennamen arbeiten, da order by nicht mit Funktionen arbeiten kann. Wenn wir die Stadt mit dem geringsten Umsatz ermitteln wollen, läuft das parallel.

mysql> select sum(Umsatz) as 'geringster Umsatz',
-> Ort as 'Ort mit geringstem Umsatz' from testerone
-> group by(Ort) order by 'geringster Umsatz' limit 1;

	geringster Umsatz		Ort mit geringstem Umsatz
	6000		Bremen

1 row in set (0.05 sec)
mysql>

Eine Angabe wie sortiert werden soll, kann in diesem Beispiel entfallen, da asc (aufsteigend) der Default ist.
Die Frage, die man sich stellen kann ist, warum das nicht mit having geht, also irgendwas in der Art.

mysql> select sum(Umsatz) as 'geringster Umsatz',
-> Ort as 'Ort mit geringstem Umsatz' from testerone
-> group by(Ort) having sum(Umsatz)=min(Umsatz);

	geringster Umsatz		Ort mit geringstem Umsatz
	6000		Bremen
	90000		Coburg
	70000		Düsseldorf
	40000		München

4 rows in set (0.00 sec)
mysql>

Was passiert ist relativ klar. Wir kriegen die Gruppen, bei denen der Mindestumsatz genau so groß ist wie der Gesamtumsatz, bei denen es also nur einen Eintrag gibt. Es ist auch nicht möglich, auf einen Schlag den zu summieren und aus den aufaddierten Werten das Minimum oder Maximum zu bestimmen.

mysql> select min(sum(Umsatz)) from Testerone group by (Ort);
ERROR 1111: Invalid use of group function
mysql>

vorhergehendes Kapitel	nach oben	nächstes Kapitel