Werbung einblenden Werbung ausblenden


Home / Tutorials / mysql Handbuch / gruppieren


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

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.
1 Peter Müller Berlin Am Hechtacker 12 mueller@wessnich.de business 30000
2 Erika Kunstig München Geibenstaig 13 kunstig@yahoo.de business 40000
3 Werner Sauerbier Freiburg Mooswaldalle 34 sauerbier@gmx.de business 4000
4 Maria Hopfendahl Berlin Kurfüstenstrasse 45 Hopf@hotmail.com consumer 5000
5 Heiner Müller Bremen Lichtergasse 12 heiner@mueller.com consumer 6000
6 Hans Kohl Düsseldorf Am Markt 45 hansiKohl@freenet.de consumer 70000
7 Werner Glottertal Coburg Am Fels 45 wernerglott@wessnich.de consumer 90000
8 Hans Lützelschwab Freiburg Am Hertweg 7 lützelschwab@freiburg.com business 20000
9 Peter Brueg Freiburg Reischstrasse 34 peter-brueg@freenet.de consumer 5000
10 Erika Lachfeld Freiburg Krozinger Strasse 12 erika_lachfeld@freiburg.net business 4000

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

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

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

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

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

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

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()

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

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

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

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