GROUP BY … HAVING

Was ist GROUP BY?

GROUP BY und HAVING gehören zu den absoluten Grundlagen der SQL Syntax, doch jeder Ninja sollte auch die Grundlagen beherrschen und sich nicht nur mit Jutsu beschäftigen, daher wollen wir uns das Konstrukt einmal ansehen. Eine Abfrage, die Daten aggregiert, indem sie beispielsweise eine Summe bildet, muss nicht immer die Summe über alle Zeilen bilden, sie kann auch die Summe über bestimmte Teilmengen der Tabelle bilden. Wie diese Teilmengen aussehen, bestimmt das GROUP BY-Konstrukt.

Wie könnt ihr mit GROUP BY arbeiten?

Um eine Abfrage mit GROUP BY zu schreiben, nehmen wir uns eine Tabelle der Wide World Importers-Datenbank vor. Angenommen, wir möchten Finanzkennzahlen aus der Datenbank herausfinden und knüpfen uns dafür die CustomerTransactions-Tabelle vor. Eine Summe der Umsätze können wir wie folgt sehen:

SELECT
       SUM(TransactionAmount)
      ,SUM(TaxAmount)
      ,SUM(OutstandingBalance)
FROM Sales.CustomerTransactions

Das Ergebnis dieser Abfrage ist zwar nett, aber nicht besonders hilfreich, da wir nun ja noch nicht wissen, in welchem Zeitraum die Transaktionen getätigt wurden, sehen wir uns also noch das TransactionDate mit hinzu:

SELECT
       SUM([TransactionAmount])
      ,SUM([TaxAmount])
      ,SUM([OutstandingBalance])
      ,[TransactionDate]
  FROM [Sales].[CustomerTransactions]

Hier informiert uns die Datenbank sofort, dass das TransactionDate in dieser Abfrage nicht zulässig ist, da es nicht in einer aggregierenden Funktion oder einem GROUP BY auftritt:

Column 'Sales.CustomerTransactions.TransactionDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Was bedeutet das? Die übrigen Spalten fasst der Server über die SUM-Aggregation zu einer einzelnen Zeile zusammen, beim Datum weiß er nicht, was er damit nun tun soll. Natürlich könnten wir auch das Datum beispielsweise über die MIN oder die MAX-Funktion aggregieren, aber das ist ja nicht, was wir möchten. Stattdessen möchten wir die Umsätze pro Tag zusammenfassen. Das können wir dann über GROUP BY wie folgt machen:

SELECT
       SUM([TransactionAmount])
      ,SUM([TaxAmount])
      ,SUM([OutstandingBalance])
      ,[TransactionDate]
  FROM [Sales].[CustomerTransactions]
  GROUP BY [TransactionDate]

Hier erhalten wir jetzt, wie gewünscht, die Umsätze auf einzelne Tage herunter gebrochen. Wir können auch nach den Tagen sortieren:

SELECT
       SUM([TransactionAmount])
      ,SUM([TaxAmount])
      ,SUM([OutstandingBalance])
      ,[TransactionDate]
  FROM [Sales].[CustomerTransactions]
  GROUP BY [TransactionDate]
  ORDER BY [TransactionDate]

Sortieren könnten wir natürlich auch wieder nur nach Spalten, die in der Ausgabe und damit entweder in einer aggregierenden Funktion oder in einem GROUP BY-Konstrukt enthalten sind. Was hingegen möglich ist ist, vom TransactionDatenur das Jahr anzuzeigen:

 SELECT
       SUM([TransactionAmount]) as [TransactionAmount]
      ,SUM([TaxAmount]) as [TaxAmount]
      ,SUM([OutstandingBalance]) as [OutstandingBalance]
      ,DATEPART(YEAR, [TransactionDate]) as [Year]
  FROM [Sales].[CustomerTransactions]
  GROUP BY [TransactionDate]
  ORDER BY [TransactionDate]

Das Ergebnis ist allerdings hier nicht ganz das gewünschte. Es erscheint nämlich nach wie Vor für jeden Tag im Jahr eine Zeile, allerdings steht in der Year-Spalte nur das Jahr des jeweiligen Datums. Um hier zu Jahressummen zu gelangen, müssen wir nach den Jahren gruppieren (und auch sortieren):

SELECT
       SUM([TransactionAmount]) as [TransactionAmount]
      ,SUM([TaxAmount]) as [TaxAmount]
      ,SUM([OutstandingBalance]) as [OutstandingBalance]
      ,DATEPART(YEAR, [TransactionDate]) as [Year]
  FROM [Sales].[CustomerTransactions]
  GROUP BY DATEPART(YEAR, [TransactionDate])
  ORDER BY DATEPART(YEAR, [TransactionDate])

Und was hat es nun mit dem “Having” auf sich? Das ist auch relativ einfach. Bilden wir zunächst Monatssummen:

SELECT
       SUM([TransactionAmount]) as [TransactionAmount]
      ,SUM([TaxAmount]) as [TaxAmount]
      ,SUM([OutstandingBalance]) as [OutstandingBalance]
      ,DATEPART(MONTH, [TransactionDate]) as [Month] 
      ,DATEPART(YEAR, [TransactionDate]) as [Year]
  FROM [Sales].[CustomerTransactions]
  GROUP BY DATEPART(YEAR, [TransactionDate]), DATEPART(MONTH, [TransactionDate])
  ORDER BY DATEPART(YEAR, [TransactionDate]), DATEPART(MONTH, [TransactionDate])

In der Abfrage seht ihr, dass es möglich ist, nach mehreren Spalten zu gruppieren. Es ist dabei bei dieser Abfrage nicht möglich, eine der beiden Gruppierungen wegzulassen…

Wenn ihr diese Abfrage nun einschränken möchtet, um nur noch das Jahr 2016 zu sehen, könnt ihr das mit einer WHERE-Bedingung tun:

SELECT
       SUM([TransactionAmount]) as [TransactionAmount]
      ,SUM([TaxAmount]) as [TaxAmount]
      ,SUM([OutstandingBalance]) as [OutstandingBalance]
      ,DATEPART(MONTH, [TransactionDate]) as [Month] 
      ,DATEPART(YEAR, [TransactionDate]) as [Year]
  FROM [Sales].[CustomerTransactions]
  WHERE DATEPART(YEAR, [TransactionDate]) = 2016
  GROUP BY DATEPART(YEAR, [TransactionDate]), DATEPART(MONTH, [TransactionDate])
  ORDER BY DATEPART(YEAR, [TransactionDate]), DATEPART(MONTH, [TransactionDate])

Was aber, wenn ihr nur die Monate finden möchtet, bei denen die OutstandingBalance nicht null ist? Das funktioniert mit der WHERE-Bedingung nicht mehr, da aggregierende Funktionen nicht in der WHERE-Bedingung zulässig sind:

SELECT
       SUM([TransactionAmount]) as [TransactionAmount]
      ,SUM([TaxAmount]) as [TaxAmount]
      ,SUM([OutstandingBalance]) as [OutstandingBalance]
      ,DATEPART(MONTH, [TransactionDate]) as [Month] 
      ,DATEPART(YEAR, [TransactionDate]) as [Year]
  FROM [Sales].[CustomerTransactions]
  WHERE SUM([OutstandingBalance]) <> 0 
  GROUP BY DATEPART(YEAR, [TransactionDate]), DATEPART(MONTH, [TransactionDate])
  ORDER BY DATEPART(YEAR, [TransactionDate]), DATEPART(MONTH, [TransactionDate])

Der Server meldet hier: 

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

Die Fehlermeldung gibt hier gleich den passenden Hinweis: HAVING ist das Zauberwort, das wir brauchen:

SELECT
       SUM([TransactionAmount]) as [TransactionAmount]
      ,SUM([TaxAmount]) as [TaxAmount]
      ,SUM([OutstandingBalance]) as [OutstandingBalance]
      ,DATEPART(MONTH, [TransactionDate]) as [Month] 
      ,DATEPART(YEAR, [TransactionDate]) as [Year]
  FROM [Sales].[CustomerTransactions]
  GROUP BY DATEPART(YEAR, [TransactionDate]), DATEPART(MONTH, [TransactionDate])
  HAVING SUM([OutstandingBalance]) <> 0 
  ORDER BY DATEPART(YEAR, [TransactionDate]), DATEPART(MONTH, [TransactionDate])

Für die alten, erfahrenen Ninjas unter Euch ist das sicher wenig überraschend, aber wir haben schon viele junge Krieger gesehen, die an der korrekten Formulierung einer aggregierten Abfrage scheitern… Diese sollten sich auch die Reihenfolge SELECT … FROM … GROUP BY … HAVING … ORDER BY einprägen.

Referenzen

• Offizielle Dokumentation von Microsoft