====== LU11b - Gruppieren mit GROUP BY ====== ===== Lernziele ===== * Ich kann mit ''GROUP BY'' eine Kennzahl pro Gruppe berechnen, zum Beispiel pro Firma oder pro Jahr. * Ich weiss, welche Spalten neben einer Aggregatfunktion in ''SELECT'' stehen dürfen. * Ich kann mit ''HAVING'' Gruppen nach einer Kennzahl filtern und erklären, worin sich ''HAVING'' von ''WHERE'' unterscheidet. {{ :de:modul:m290_guko:learningunits:lu10:theorie:group_by-sum.webp?900 |GROUP BY fasst Zeilen mit gleichem Wert zu einer Gruppe zusammen}} Ohne ''GROUP BY'' liefert eine Aggregatfunktion **eine** Zahl über alle Zeilen. Mit ''GROUP BY'' bekommen Sie **eine Zeile pro Gruppe**, also zum Beispiel die Anzahl Starts für jede Firma einzeln. ===== 1) Ein erstes Beispiel: Starts pro Firma ===== SELECT company, COUNT(*) AS starts FROM missions GROUP BY company ORDER BY starts DESC LIMIT 10; ^ company ^ starts ^ | RVSN USSR | 1777 | | CASC | 338 | | Arianespace | 293 | | General Dynamics | 251 | | VKS RF | 216 | | NASA | 203 | | SpaceX | 182 | | US Air Force | 161 | | ULA | 151 | | Boeing | 136 | ''GROUP BY company'' legt alle Zeilen mit derselben Firma zusammen. ''COUNT(*)'' zählt dann pro Firma. ''ORDER BY'' und ''LIMIT'' kennen Sie schon. Was die Firmen-Abkürzungen bedeuten, steht am Ende dieser Seite. ===== 2) Die Regel für SELECT ===== Die Regel: Neben einer Aggregatfunktion darf in ''SELECT'' nur eine Spalte stehen, nach der gruppiert wird. Warum das so ist: Schauen wir uns den typischen Fehler an: SELECT gemeindename, SUM(straftaten_total) FROM einbrueche; MySQL meldet Fehler 1140: //In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column ...// Ohne ''GROUP BY'' fasst ''SUM'' die **ganze Tabelle** zu einer einzigen Zahl zusammen: Die Summen aller Fälle über alle 8298 Zeilen. Das Resultat hat also eine Zeile. Für ''gemeindename'' hat es im Resultat damit nur Platz für einen Namen, aber in der Tabelle stehen 161 verschiedene Gemeinden. Welche soll MySQL anzeigen? Darauf gibt es keine richtige Antwort, deshalb meldet MySQL einen Fehler. Mit ''GROUP BY gemeindename'' ändert sich die Frage. Jetzt gibt es **eine Zeile pro Gemeinde**, und in jeder Zeile steht genau ein Gemeindename. Hier mit drei Gemeinden als Beispiel: ^ gemeindename ^ SUM(straftaten_total) ^ | Dietikon | 2985 | | Kilchberg | 1005 | | Winterthur | 10408 | ===== 3) GROUP BY mit WHERE: Einbrüche pro Jahr ===== Wie viele Einbrüche gab es in den letzten fünf Jahren im ganzen Kanton? SELECT ausgangsjahr, SUM(straftaten_total) AS total_faelle FROM einbrueche WHERE tatbestand = 'Einbrüche insgesamt' GROUP BY ausgangsjahr ORDER BY ausgangsjahr DESC LIMIT 5; ^ ausgangsjahr ^ total_faelle ^ | 2024 | 7998 | | 2023 | 7087 | | 2022 | 6935 | | 2021 | 5804 | | 2020 | 6875 | ''WHERE'' wählt zuerst nur die Zeilen «Einbrüche insgesamt» aus. Danach fasst ''GROUP BY'' die übrigen Zeilen pro Jahr zusammen, und ''SUM'' zählt die Fälle pro Jahr. Von diesen fünf Jahren gab es 2021 am wenigsten Einbrüche, seither steigt die Zahl wieder. Ohne die ''WHERE''-Zeile kämen für 2024 15996 Fälle heraus, genau das Doppelte von 7998. Die Abfrage läuft ohne Fehler durch, das Resultat ist trotzdem falsch. Schauen Sie sich eine Tabelle deshalb an, bevor Sie zusammenzählen. ===== 4) HAVING: Gruppen nach einer Kennzahl filtern ===== In welchen Jahren gab es mehr als 12'500 Einbrüche? ''WHERE'' hilft hier nicht weiter. ''WHERE'' filtert einzelne Zeilen, bevor gruppiert wird, und kennt die Summe pro Jahr noch gar nicht. Für Bedingungen an eine Kennzahl gibt es ''HAVING''. Es filtert die Gruppen, nachdem gruppiert wurde. SELECT ausgangsjahr, SUM(straftaten_total) AS total_faelle FROM einbrueche WHERE tatbestand = 'Einbrüche insgesamt' GROUP BY ausgangsjahr HAVING SUM(straftaten_total) > 12500 ORDER BY total_faelle DESC; ^ ausgangsjahr ^ total_faelle ^ | 2009 | 14986 | | 2012 | 14365 | | 2010 | 13385 | ^ ^ WHERE ^ HAVING ^ | Filtert | einzelne Zeilen | Gruppen | | Zeitpunkt | vor dem Gruppieren | nach dem Gruppieren | | Aggregatfunktionen erlaubt? | nein | ja | | Beispiel | ''%%WHERE tatbestand = 'Einbrüche insgesamt'%%'' | ''HAVING SUM(straftaten_total) > 12500'' | ===== 5) Die Reihenfolge der Klauseln ===== Eine Abfrage mit allen Teilen sieht so aus. Die Reihenfolge ist fest, einzelne Teile dürfen fehlen. SELECT spalte, AGGREGATFUNKTION(spalte) AS name FROM tabelle WHERE ... -- welche Zeilen GROUP BY spalte -- wie gruppieren HAVING ... -- welche Gruppen ORDER BY ... -- in welcher Reihenfolge LIMIT ...; -- wie viele Zeilen Sie dürfen auch nach einem berechneten Wert gruppieren, zum Beispiel nach dem Jahr eines Datums: SELECT YEAR(launch_date) AS startjahr, COUNT(*) AS starts FROM missions GROUP BY startjahr; ''YEAR(...)'' holt das Jahr aus dem Datum. In MySQL darf in ''GROUP BY'' der Alias ''startjahr'' stehen. ===== Die Firmen im Raumfahrt-Datensatz ===== ^ Kürzel ^ Organisation ^ Land, Region ^ | RVSN USSR | Strategische Raketentruppen der Sowjetunion | Sowjetunion (historisch) | | CASC | China Aerospace Science and Technology Corporation | China | | Arianespace | Arianespace SA, europäischer Startdienstleister | Europa, Frankreich | | General Dynamics | General Dynamics Astronautics, u.a. Atlas-Programm (historisch) | USA | | VKS RF | Luft- und Weltraumstreitkräfte Russlands | Russland | | NASA | National Aeronautics and Space Administration | USA | | SpaceX | Space Exploration Technologies Corp. | USA | | US Air Force | United States Air Force (historischer Betreiber) | USA | | ULA | United Launch Alliance, Joint Venture von Boeing und Lockheed Martin | USA | | Boeing | The Boeing Company, u.a. Delta-Programm | USA | ---- {{tag>M290-LU11}} [[https://creativecommons.org/licenses/by-nc-sa/4.0/|{{https://i.creativecommons.org/l/by-nc-sa/4.0/88x31.png}}]] Guido Koch