Mit Aggregat-Funktionen und GROUP BY Daten auswerten und aggregieren

• » Home
• »Sql-Tutorial
• » Group By

Vorbemerkung

• Mit SELECT, FROM und WHERE erhalten Sie einzelne Zellen der gewählten Tabellen zurück. Es wird jedoch keine Aggregation des Ergebnisses durchgeführt, die Daten werden nicht zusammengefaßt. Liefert Ihre JOIN-Anweisung 50.000 Zeilen und entfernt die WHERE-Klausel hiervon 30.000, so enthält Ihre Ergebnistabelle 20.000 Zeilen. Alle Techniken mit Aggregatfunktionen fassen dagegen mehrere Zeilen zu einer Ergebniszeile zusammen und geben nur noch diese aus.
• Eine solche Aggregation / Zusammenfassung / Ermittlung von Kennziffern / Konsolidierung kann sich über die gesamte Tabelle erstrecken. In diesem Fall werden nur Aggregatfunktionen, jedoch keine GROUP BY - Klausel benötigt. Oder die Kennziffern sollen aufgesplittet nach Artikeln, Verkäufern, Datum, Regionen oder anderen Kriterien bestimmt werden. In diesem Fall wird die Tabelle zunächst gemäß dieser Kriterien mit der GROUP BY - Klausel in Teiltabellen zerlegt und für jede Teiltabelle die Kennziffern berechnet. Damit liegt pro Teiltabelle eine Ergebniszeile vor, eventuell sind die konkreten Werte, die über die Zugehörigkeit zu dieser Teiltabelle entscheiden, ebenfalls mit ausgegeben. Aus all diesen Einzelzeilen wird das Gesamtergebnis zusammengefügt.
• Bildlich gesprochen: Die bisherigen Techniken liefern Ihnen einen Stapel von Zeilen zurück. Verwenden Sie eine Aggregat-Funktion, so wird der Zeilenstapel auf eine Zelle, einen einzigen Wert gestaucht. Verwenden Sie mehrere Aggregat-Funktionen, so wird der Zeilenstapel auf mehrere Zellen komprimiert, diese werden als eine Zeile ausgegeben. Gruppieren Sie das Ergebnis zusätzlich mit GROUP BY, so wird der Gesamtstapel zunächst gemäß der GROUP BY - Kriterien in Teilstapel zerlegt, über jeden dieser Teilstapel wird aggregiert, dieser also in einer Zeile zusammengefaßt, so daß am Ende alle Teilstapel-Ergebniszeilen zum Gesamtergebnis vereinigt werden.
• Sollen Ergebnisse, die mittels der Aggregatfunktionen ermittelt wurden, als zusätzliches Filterkriterium herangezogen werden, so können solche Ausdrücke in der HAVING-Klausel angegeben werden.

Syntax

• Rückgabe einer einzelnen Zelle - einfache Aggregation

  SELECT <Aggregatfunktion(Spaltenname) As [Spaltenname]>
  FROM ...
  [WHERE ...]

• Rückgabe einer Zeile mit mehreren Zellen - mehrere Kennziffern gleichzeitig ermitteln

  SELECT <Aggregatfunktion-1(Spaltenname-A1) As Ausgabename-1,>
  	<Aggregatfunktion-2(Spaltenname-A2) As Ausgabename-2,>
  	...
  	<Aggregatfunktion-n(Spaltenname-An) As Ausgabename-n>
  FROM ...
  [WHERE ...]
  [HAVING ...]

• Gruppierung in Teilgruppen, Aggregation über die Teilgruppen und Zusammenfassen der Einzelergebnisse

  SELECT <Aggregatfunktion-1(Spaltenname-A1) As Ausgabename-1,>
  	<Aggregatfunktion-2(Spaltenname-A2) As Ausgabename-2,>
  	...
  	<Aggregatfunktion-n(Spaltenname-An) As Ausgabename-n>,
  	Spaltenname-1,
  	Spaltenname-2,
  	...
  	Spaltenname-m
  
  FROM ...
  [WHERE ...]
  GROUP BY Spaltenname-1,
  	Spaltenname-2,
  	...
  	Spaltenname-m
  [HAVING ...]
  

Beispiele

1. SELECT COUNT(A.DATUM) As [Zahl-der-Umsätze]
   FROM UMSATZ As A

   Beachten Sie, daß Ihnen diese Abfrage 9 als Ergebnis zurückliefert, also die Zahl der nichtleeren Zellen. Fügen Sie einen weiteren Datensatz ein und lassen bei diesem das Feld DATUM leer, so erhalten Sie mit der obigen Abfrage weiterhin das Ergebnis 9. Es wird nicht 2 (die Zahl der verschiedenen Datumsangaben) ausgegeben. Sind alle Zellen einer Tabelle belegt, so ist es für diese Abfrage mit COUNT unerheblich, welche Zelle Sie verwenden.

2. SELECT A.DATUM, COUNT(A.DATUM) As [Zahl-der-Umsätze-pro-Tag]
   FROM UMSATZ As A
   GROUP BY A.DATUM

   Sie erhalten als Ergebnis zwei Zeilen, für jede der unterschiedlichen Datumsangaben eine Zeile. Jede Zeile enthält die zusätzliche Zelle 'Zahl-der-Umsätze-pro-Tag' mit den Werten 6 für den 24.06 und 3 für den 25.06.99. Verzichten Sie auf die Ausgabespalte A.DATUM, so können Sie die beiden Zellen mit den Werten 6 und 3 nicht mehr interpretieren.

3. SELECT MAX(A.A_PREIS) As [teuerster-Artikel]
   FROM ARTIKEL As A

   Dies liefert Ihnen den Preis des teuersten Artikels (360.00). Beachten Sie, daß Sie mit dieser Abfrage noch nicht den Namen oder die Details dieses Artikels erfahren. Dies gelingt nur, wenn Sie das obige Ergebnis selbst als Kriterium in einer Where-Abfrage verwenden. Näheres finden Sie im Abschnitt über Unterabfragen. Hier eine Möglichkeit:

   SELECT B.*
   FROM ARTIKEL As B
   WHERE B.A_PREIS = (SELECT MAX(A.A_PREIS) As [teuerster-Artikel]
   	FROM ARTIKEL As A)

   Ergebnis:

   A_NR A_NAME A_PREIS 22 Mantel 360.00

4. SELECT SUM(A.A_PREIS * B.A_STUECK) As [Gesamtumsatz]
   FROM ARTIKEL As A INNER JOIN UMSATZ As B
   ON A.A_NR = B.A_NR

   Diese Abfrage kombiniert jede Umsatz-Zeile mit der zu ihr gehörenden Artikel-Zeile. Damit sind die Spalten A_PREIS und A_STUECK bekannt und können miteinander multipliziert werden. Ohne die Aggregatfunktion SUM würden neun Zeilen mit dem tatsächlichen Umsatz dieser einzelnen Verkaufshandlung ausgegeben. So wird über all diese Einzelergebnisse summiert und der Gesamtumsatz von 27.472,00 ermittelt.

5. SELECT A.A_NR, SUM(A.A_PREIS * B.A_STUECK) As [Umsatz-dieses-Artikels]
   FROM ARTIKEL As A INNER JOIN UMSATZ As B
   ON A.A_NR = B.A_NR
   GROUP BY A.A_NR

   Ergebnis:

   A_NR Umsatz-dieses-Artikels 11 4.862,00 12 1.990,00 13 4.420,00 22 16.200,00

    

   Diese Aggregation zerlegt den Gesamtstapel der neun Umsätze zunächst in Teilstapel anhand der Artikelnummern. Für jeden Teilstapel wird multipliziert und die Summe ermittelt. Das Ergebnis wird, ergänzt um die Information, zu welchem Artikel die Summation gehört, ausgegeben.

6. SELECT B.DATUM,
   	MAX(A.A_PREIS * B.A_STUECK) As [Max-Tages-Einzelumsatz]
   FROM ARTIKEL As A INNER JOIN UMSATZ As B
   ON A.A_NR = B.A_NR
   GROUP BY B.DATUM

   Ergebnis:

   DATUM Max-Tages-Einzelumsatz 24.06.1999 3.867,50 25.06.1999 12.600,00

    

   Anstelle der obigen Summation wird das Maximum über alle Einzelumsätze für jeden Tag ermittelt. Beachten Sie, daß Sie auch mit dieser Abfrage nicht ermitteln können, welcher Artikel für diesen höchsten Umsatz verantwortlich ist. Denn sobald Sie zur Gruppierung die Artikel-Nummer hinzunehmen, erhalten Sie für jede Kombination aus Artikel und Tag eine Zeile, so daß das Maximum über solche einzeiligen Teilmengen gleich dem Wert in dieser Zelle ist. Möchten Sie zusätzlich wissen, welcher Artikel für diesen höchsten Einzelumsatz zuständig ist, so kombinieren Sie diese Abfrage als JOIN mit einer direkten Multiplikation:

   SELECT A.DATUM, A.A_NR, A.Einzelumsatz
   
   FROM
   	(SELECT C.DATUM, B.A_NR,
   		B.A_PREIS * C.A_STUECK As [Einzelumsatz]
   	FROM ARTIKEL As B INNER JOIN UMSATZ As C
   	ON B.A_NR = C.A_NR) AS A
   
   INNER JOIN (
   	SELECT Y.DATUM,
   		MAX(X.A_PREIS * Y.A_STUECK) As [Max-Tages-Einzelumsatz]
   	FROM ARTIKEL As X INNER JOIN UMSATZ As Y
   	ON X.A_NR = Y.A_NR
   	GROUP BY Y.DATUM) As D
   
   ON (A.DATUM = D.DATUM) AND
   	(A.Einzelumsatz = D.[Max-Tages-Einzelumsatz])

   Ergebnis:

   DATUM A_NR Einzelumsatz 24.06.1999 13 3.867,50 25.06.1999 22 12.600,00

    

   Unterstützt das Datenbanksystem - im Gegensatz zu Access - eine direkte Multiplikation im JOIN-Ausdruck, so können Sie den ersten geklammerten JOIN-Ausdruck herausziehen und die Multiplikation direkt in der JOIN-Klausel durchführen.

7. SELECT B.DATUM, B.A_NR,
   	SUM(A.A_PREIS * B.A_STUECK) As Tagesumsatz
   FROM ARTIKEL As A INNER JOIN UMSATZ As B
   ON A.A_NR = B.A_NR
   GROUP BY B.DATUM, B.A_NR

   Ergebnis:

   DATUM A_NR Tagesumsatz 24.06.1999 11 3.094,00 24.06.1999 12 1.990,00 24.06.1999 13 4.420,00 24.06.1999 22 3.600,00 25.06.1999 11 1.768,00 25.06.1999 22 12.600,00

    

   Das ist nun die klassische ausdifferenzierte Version: Die tatsächlichen Einzelumsätze werden pro Tag und pro Artikel aggregiert, das Ergebnis wird mit den beiden gruppierenden Spalten ausgegeben.

8. SELECT A.DATUM, A.A_NR, COUNT(*) As [Umsatz-Anzahl]
   FROM UMSATZ As A
   GROUP BY A.DATUM, A.A_NR
   HAVING COUNT(*) > 1

   Ergebnis:

   DATUM A_NR Umsatz-Anzahl 24.06.1999 12 2 24.06.1999 13 2 25.06.1999 11 2

    

   Ohne HAVING-Klausel würde diese Abfrage sechs Zeilen zurückliefern - vier für den 24.06, an dem vier Artikel verkauft wurden, 2 für den 25.06. Die HAVING-Klausel definiert eine zusätzliche Einschränkung und beschränkt die Ausgabe auf jene Artikel, die mindestens zwei Mal verkauft worden sind.

9. SELECT A.A_NR, SUM(A.A_PREIS * B.A_STUECK) As [Umsatz-dieses-Artikels]
   FROM ARTIKEL As A INNER JOIN UMSATZ As B
   ON A.A_NR = B.A_NR
   GROUP BY A.A_NR
   HAVING SUM(A.A_PREIS * B.A_STUECK) > 10000
   

   Dies ermittelt jene Artikel, mit welchen ein Umsatz von über 10.000 gemacht wurde. Die SQL-Abfrage ist dieselbe wie unter Beispiel 5, es wurde nur die HAVING-Klausel hinzugefügt. Artikel 22 wird ausgegeben.

Aggregatfunktionen

1. COUNT: Ermittlung der Zeilen bzw. Zellen. Diese Funktion kann in zwei verschiedenen Versionen genutzt werden: COUNT(*) gibt die Anzahl aller Zeilen gemäß dem GROUP-BY-Abschnitt zurück. COUNT(<Spaltenname>) liefert die Zahl der nichtleeren Zellen in der angegebenen Spalte.
2. MIN(<Spaltenname>) / MAX(<Spaltenname>): Ermittelt das Minimum / Maximum der angegebenen Spalte.
3. SUM(<Spaltenname>): Summation über alle Zellwerte
4. AVG(<Spaltenname>): Mittelwert aller Zellwerte, NULL-Werte werden ignoriert.

Dies sind die Funktionen, die jedes Datenbanksystem mit Sql-Schnittstelle anbieten dürfte. Hinweise für weitere Funktionen Ihres Systems finden Sie in der zugehörigen Dokumentation. Die Aggregatfunktionen mit numerischem Bezug (SUM, AVG) können nur für Spalten verwendet werden, welche numerische Datentypen enthalten.

Bemerkungen

• Wenn Sie eine Spalte ausgeben, über diese jedoch weder aggregieren noch sie in der GROUP-BY-Klausel erwähnen, so erhalten Sie eine typische Fehlermeldung: 'Sie wollten eine Abfrage ausführen, die den angegebenen Ausdruck 'A_NR' nicht als Teil der Aggregatfunktion einschließt' (Beispiel Access). Diese Fehlermeldung ist insofern irreführend, da es drei Möglichkeiten gibt: Sie entfernen die Spalte aus der Liste der Ausgabespalten, Sie fügen die Spalte zum GROUP-BY-Abschnitt hinzu oder Sie aggregieren über diese Spalte. Beim MS-SQLServer heißt die analoge Fehlermeldung: 'Die U.A_NR-Spalte ist in der Auswahlliste ungültig, da sie nicht in einer Aggregatfunktion enthalten und keine GROUP BY-Klausel vorhanden ist'. Wurde eine GROUP-BY-Klausel angegeben, so wird Ihnen mitgeteilt: 'und nicht in der GROUP BY-Klausel enthalten ist'. Beim MS-SqlServer ist die Fehlermeldung also weitaus spezifischer als bei Access.
• Ist zusätzlich ein WHERE-Abschnitt angegeben, so wird dieser zuerst kostengünstig ausgewertet. Die Aggregation mit eventueller vorheriger Gruppierung wird erst anschließend ausgeführt. In den HAVING-Abschnitt gehören deshalb nur jene Ausdrücke, welche sich auf Aggregationen beziehen.
• Access neigt bei der visuellen Codegenerierung dazu, bei vorhandenen Aggregationen alle Kriterien von der WHERE-Bedingung in die HAVING-Klausel zu verschieben. Dies ist jedoch unnötig teuer und sollte gegebenenfalls per Hand bzw. durch den Verzicht auf die visuelle Codierung vermieden werden.
• Wenn Sie nur wissen möchten, welche verschiedenen Zeilen existieren, so liefern die beiden Alternativen DISTINCT sowie das Gruppieren nach allen Ausgabespalten dieselben Ergebnisse. DISTINCT ist jedoch immer ressourcenschonender als GROUP BY und deshalb in solchen Fällen erste Wahl.
• In PHP-Foren findet sich wiederholt Code, mit welchem die Zahl der Zeilen in einer Tabelle ermittelt werden soll. Dieser Code ist wie folgt aufgebaut:

  $select = mysql_query("Select * From Artikel");
  $reihen = mysql_num_rows($select);

  Eine solche Verarbeitung widerspricht sämtlichen Prinzipien ressourcenschonender Programmierung. Akzeptabel ist eine Lösung der Form:

  $select = mysql_query("Select Count(*) From Artikel");
  $zahl_der_zeilen = mysql_fetch_row($select);
  echo $zahl_der_zeilen[0];
  

  Ein DBMS sollte nur jene Zeilen zurückgeben, die tatsächlich benötigt werden. Das Zählen von Datensätzen wird von jedem DBMS rascher erledigt als im Rahmen eines externen Aufrufs von der umgebenden Wirtssprache. Ferner wird nur eine einzige Zelle, also ein Gebilde aus einer Spalte und einer Zeile, zurückgegeben. Bei gespeicherten Prozeduren läßt sich der Code weiter optimieren:

  Create Procedure _up_count_Artikel
  	@num_rows int Output
  As
  
  Select @num_rows = Count(*)
  From Artikel

  In diesem Fall muß keine DataTable oder ein RecordSet mit Informationen über die zurückgegebenen Datentypen erstellt werden, da der Rückgabewert von vornherein bekannt ist. Der Aufruf kann nun (VB.NET) so erfolgen:

  Dim oCmd As New SqlCommand("_up_count_Artikel", oConn), _
  	i_num_rows As Integer
  
  oCmd.Parameters.Add("@num_rows", SqlDbType.Int).Direction = _
  	ParameterDirection.Output
  Try
  	oCmd.ExecuteNonQuery()
  	i_num_rows = CType(oCmd.Parameters("@num_rows").value, Integer)
  
  Catch _e As Exception
  	'Fehlerbehandlung
  End Try