Am 14. September 2017 haben wir eine überarbeitete Fassung unserer Datenschutzrichtlinie veröffentlicht. Wenn Sie video2brain.com weiterhin nutzen, erklären Sie sich mit diesem überarbeiteten Dokument einverstanden. Bitte lesen Sie es deshalb sorgfältig durch.

SQL Grundkurs 2: Aufgaben und Lösungen

Beispiel 17: Gruppierung mit Gesamtsumme

LinkedIn Learning kostenlos und unverbindlich testen!

Jetzt testen Alle Abonnements anzeigen
Ermitteln Sie die Anzahl an Artikeln der Gruppen Besteck, Geschirr und Küchengeschirr je Preisstufe in Zehnerschritten. Lassen Sie am Ende der Liste die Gesamtanzahl anzeigen.

Transkript

In diesem Video wollen wir die Gruppierung nach einem Ausdruck üben. Es geht darum, die Anzahl der Artikel je Preisgruppe für die Artikelgruppen Besteck, Geschirr und Küchengeschirr zu ermitteln. Die Preisstufen sollen so gestaltet sein, dass sie in Zehnerschritten gebildet werden, immer aufgerundet, d. h. Preisstufe zehn heißt von null bis zehn Euro, und die Anzahl, das wären hier 208 Stück. 10,001 bis 20 Euro, dann 129 usw. Jene Preisgruppen, wo es keine Artikel gibt, die fehlen natürlich. 201 heißt eben in dem Fall von 200 bis 210 Euro gibt's einen Artikel. Zusätzlich können Sie wenn Sie möchten am Ende mittels ROLLUP eine Gesamtanzahl ausgeben. 498 Artikel gehören eben diesen drei Gruppen, Besteck mit dem Kürzel (BE), Geschirr mit dem Kürzel (GE) und Küchengeschirr mit dem Kürzel (KG) an. Gruppieren Sie also nach der Berechnung der Preisstufe. Dazu müssen Sie auf ganze Zehner aufrunden und nach diesem Ausdruck gruppieren. Für den enthaltenen Artikel bilden Sie die Anzahl. Für die Bildung der Preisstufe ziehen Sie den Verkaufspreis, das ist die Spalte "vkpreis" der Tabelle "artikel" heran. Schalten Sie nun auf Pause und setzen Sie das Beispiel um. Wir beginnen nun uns die Lösung gemeinsam anzusehen und starten mit dem Microsoft SQL Server. Der erste Schritt ist einmal, wie kann ich die Preisstufe errechnen? Dafür können wir die Funktion CEILING verwenden. CEILING rundet immer auf. CEILING würde ... da gibt's leider nicht so wie bei ROUND einen Parameter, nach wieviel Kommastellen ich runden möchte ... CEILING rundet immer auf ganze Zahlen auf, d. h. also, wenn ich auf Zehner aufrunden möchte, muss ich durch zehn dividieren dann aufrunden, und anschließend wieder mit zehn multiplizieren. So kommen wir zur Preisstufe. Damit ich nach dieser Preisstufe gruppieren kann, muss ich genau diesen Ausdruck hier in die GROUP BY-Klausel einsetzen. Zusätzlich habe ich hier noch ROLLUP verwendet, um die Gesamtsumme zu bilden. Vielleicht zum einfachen Verständnis, mache ich das Ganze vorweg einmal ohne den ROLLUP also nur mit GROUP BY. d. h. ich lasse diesen hinteren Teil, auch das ORDER BY nochmal weg, d. h. ich gruppiere hier nach diesem Ausdruck, nach meiner Preisstufe. Den Alias-Namen "preisstufe" kann ich ja bei der GROUP BY-Klausel nicht verwenden, ich muss den ursprünglichen Ausdruck hier einbauen. Damit wir nur für die drei genannten Artikelgruppen das Ergebnis eruieren, habe ich hier eine WHERE-Klausel ergänzt, mit "gruppe IN" und die Kürzel der drei Gruppen angefügt. Ich markiere einmal nur diesen Teil der Anweisung, um ihn auszuführen, und wir sehen jetzt einmal hier, die gebildeten Preisstufen, mit der entsprechenden Anzahl. Damit ich nun die Gesamtanzahl auch bekomme, kann ich jetzt hier das ROLLUP ergänzen, In Klammern führe ich hier den Ausdruck an d. h. ich eruiere für diesen Ausdruck einmal die Gesamtsumme. Ich markiere jetzt diesen Teil der Anweisung und führe ihn aus. Nun bekomme ich hier am Ende eine Gesamtanzahl. Damit ich sauber sortieren kann bei ROLLUP gibt es diese GROUPING-Funktion. Denn was könnte passieren, wenn ich jetzt z. B. sage "ORDER BY preisstufe". Dann wandert jetzt z. B. hier, beim SQL Server die Summe nach oben, denn die preisstufe NULL, Nullwerte werden beim SQL Server am Beginn angefügt. Damit ich hier sauber sortieren kann, dafür gibt es ja diese GROUPING-Funktion In die GROUPING-Funktion füge ich den gleichen Ausdruck ein, den ich hier fürs ROLLUP verwendet habe. und die Funktion GROUPING liefert mir einen Wert, ob eine Zeile im Ergebnis aufgrund von ROLLUP oder GROUPING generiert wurde, oder eine normale Zeile ist. Normale Zeilen liefern "0", und in dem Fall "1" für diese erste Gesamtsumme auf erster Ebene. Und wenn ich das fürs Sortieren verwende als erstes Kriterium, bekomme ich immer die normalen Zeilen zuerst die NULL-Zeile, und in unserem Fall die Einser-Zeile dahinter. Wenn ich danach noch nach Preisstufe sortiere, dann schadet das nicht in meinem Ergebnis, und ich bekomme, auch wenn ich hier sortiere, in dem Fall absteigend nach der Anzahl, bekomme ich hier die Gesamtsumme am Ende. Der Microsoft SQL Serverunterstützt beide gebräuchlichen ROLLUP-Syntaxvarianten. Die erste, die ich Ihnen gezeigt habe, GROUP BY ROLLUP und in Klammer ist die typische, die man verwendet, aber auch die Variante, die wir z. B. bei MySQL dann verwenden werden. WITH ROLLUP wird hier unterstützt, d. h. an Stelle hier vorne eine Funktion einzusetzen, wird hinter der Gruppierung einfach der Zusatz WITH ROLLUP ergänzt. das liefert mir dasselbe Ergebnis. das liefert mir dasselbe Ergebnis. Welche Variante Sie hier verwenden, spielt also keine Rolle. Jetzt haben wir hier mehrmals diesen Ausdruck verwenden müssen, nämlich CEILING, zum Berechnen der Preisstufe dreimal. Wenn Sie sich das sparen möchten und den Ausdruck nicht jedes Mal neu einbauen könnte ich Ihnen den Tipp anbieten, als Alternative hier eine Unterabfrage zu verwenden. Und zwar sehen Sie, ich habe jetzt hier in einer Unterabfrage vorweg einmal die Preisstufe errechnet. Ich führe die Anweisung alleine aus, und Sie sehen hier jetzt einfach mal alle Preisstufen. Und nun für alle 498 Zeilen nämlich. Und nun greife ich in der Hauptabfrage auf dieses Zwischenergebnis zu. Wichtig ist, dass ich hier für diese Unterabfrage, die ich in Klammern gesetzt habe, einen Tabellen-Alias ... ich habe hier ein "x" verwendet ... vergeben habe. Und nun greife ich hier auf diesen Berechnungsausdruck mit dem Aliasnamen "preisstufe" zu. Da ich nicht auf der selben Ebene, sondern eine Ebene darüber mich befinde, kann ich das machen. Ich kann auf den Aliasnamen zugreifen. Das ist jetzt sehr komfortabel, weil jetzt brauche ich in der Hauptabfrage nunmehr verwenden "SELECT preisstufe, COUNT(preisstufe) AS artikel", also die Anzahl, "GROUP BY ROLLUP(preisstufe)", und auch hier "ORDER BY GROUPING(preisstufe)". Damit wird die Anweisung sehr kurz und sehr überschaubar, liefert aber dasselbe Ergebnis. Wenn es Sie jetzt noch stört, beim Microsoft SQL Server, dass hier die Kommastellen ... weil es ein Währungsdatentyp ist ... mit angezeigt werden, dann können wir abschließend noch etwas machen. Wir können das Ergebnis der Preisstufe mit der CAST-Funktion ... also mit CONVERT, können Sie auch verwenden ... in einen Integer umwandeln. und damit würden wir dann dasselbe Ergebnis, allerdings hier ohne die entsprechenden Kommastellen bekommen. Das wäre jetzt eine Variante, die man ersatzweise verwenden könnte. Schauen wir uns noch vergleichend die Lösungen für Oracle und MySQL an. Die Anweisung bei Oracle ist fast ident. Das GROUP BY, das GROUPING, das ROLLUP, funktioniert auf die gleiche Art und Weise. Der Unterschied ist in der Berechnung der Preisstufe, denn die Funktion zum Ab- und Aufrunden ist unterschiedlich. Oracle ist ja sehr sparsam bei Funktionen, deshalb gibt es keine Funktion zum Aufrunden. Ich muss hier immer abrunden, und eine Einheit dazuzählen. Mit der Funktion TRUNC, ähnlich wie ROUND auf "-1" runde ich sozusagen auf Zehner, auf eine Kommastelle vor dem Komma "-1" ab. "+10" bedeutet dann, ich gebe zehn dazu und runde damit auf, auf die höhere Zehnerstelle. Und das ist jetzt der Ausdruck, den ich hier verwende um die Preisstufen zu bilden. Alles andre bleibt hier ident, so wie wir es beim Microsoft SQL Server gesehen haben. Auch hier verwende ich GROUPING fürs Sortieren, um sicherzugehen, dass das sauber funktioniert. Und auch hier können wir die Alternative mit der Unterabfrage verwenden. Da gibt's keinen Unterschied in der Syntax. Der einzige Unterschied ist hier, in diesem Berechnungsausdruck zu finden Und ich führe auch diese Anweisung aus, und wir sehen, ich bekomme hier dasselbe Ergebnis. Da Oracle standardmäßig keine Kommastellen darstellt, wenn sie nicht in Verwendung sind, brauche ich hier auch nichts tun, um diese aus dem Ergebnis zu entfernen. Abschließend noch MySQL. Da gibt es einen kleinen Unterschied. Die Berechnung der Preisstufe ist wieder ident, denn die Funktionen FLOOR oder CEILING sind bei MySQL gleich wie beim Microsoft SQL Server. Das brauche ich Ihnen nicht erklären. Der Unterschied liegt darin ... ich habe es Ihnen auch beim Microsoft SQL Server schon als Alternative gezeigt ... dass nicht "GROUP BY ROLLUP()" geschrieben werden muss, sondern MySQL unterstützt nur die Syntaxvariante mit WITH ROLLUP am Ende. Alternativ gibt es auch die Funktion TRUNCATE, die gleich funktioniert wie die Funktion TRUNC bei Oracle, nur wird es hier TRUNCATE ausgeschrieben. und Sie können daher auch diesen Ausdruck verwenden, um abzurunden und dann mit "+10" aufzurunden. Also beide Berechnungsmethoden, die mit CEILING oder mit TRUNCATE, können Sie beim MySQL-Produkt einsetzen. Und auch hier bekommen Sie eine Gesamtzahl. Was wir jetzt aber berücksichtigen müssen, ist ... vielleicht ist es Ihnen aufgefallen, dass ich hier kein ORDER BY verwendet habe. Das liegt daran, dass MySQL die Kombination von ROLLUP und ORDER BY nicht unterstützt. Was müsste ich jetzt machen, wenn ich jetzt z. B. aber hier absteigend nach den Preisstufen sortieren möchte und nicht aufsteigend und trotzdem am Ende die Gesamtanzahl haben möchte. Da gibt es jetzt eine Sonderform, ich kann ja keine ORDER BY-Klausel verwenden. aber in der GROUP BY-Klausel kann ich optional vor dem WITH ROLLUP, also direkt hinter der Gruppierung auch das Schlüsselwort "DESC" für "descending", für absteigend einbauen. Und wenn ich das jetzt hier ausführe, dann habe ich jetzt zwar nach der Preisstufe absteigend die Sortierung, ohne ORDER BY-Klausel, und auch am Ende die Gesamtanzahl. Das heißt das kann ich damit lösen, denn auch das GROUPING, die Funktion in der Form gibt es bei MySQL nicht. Und sie würde mir auch nichts nützen, da ich ja in dieser Kombination mit ROLLUP gar keine ORDER BY-Klausel einsetzen kann. Was wir also in diesem Beispiel gesehen haben ist, dass wir auch nach Ausdrücken gruppieren können, indem wir diese einfach in die GROUP BY-Klausel übernehmen. Wenn wir Gesamtanzahl verwenden möchten, kann ich das mit dem Zusatz ROLLUP machen. Das kann ich entweder in der Syntax WITH ROLLUP oder so wie wir es bei Microsoft und Oracle gesehen haben, mit GROUP BY ROLLUP bewerkstelligen. Beim MySQL Server kann ich die Sortierung direkt beim Gruppieren absteigend definieren. Bei Oracle und dem Microsoft SQL Server verwende ich die GROUPING-Funktion um sicherzustellen, dass ich eine saubere Sortierung in so einem Szenario bekomme.

SQL Grundkurs 2: Aufgaben und Lösungen

Vertiefen Sie Ihre SQL-Kenntnisse. In diesem Workshop erhalten Sie zahlreiche Beispielaufgaben, die Sie selbst lösen können. Anschließend zeigt Ihnen der Trainer die Lösung.

7 Std. 41 min (61 Videos)
Derzeit sind keine Feedbacks vorhanden...
 

Dieses Training setzt SQL-Kenntnisse voraus, wie sie beispielsweise in „SQL lernen und anwenden“ vermittelt werden.

Dieser Online-Kurs ist als Download und als Streaming-Video verfügbar. Die gute Nachricht: Sie müssen sich nicht entscheiden - sobald Sie das Training erwerben, erhalten Sie Zugang zu beiden Optionen!

Der Download ermöglicht Ihnen die Offline-Nutzung des Trainings und bietet die Vorteile einer benutzerfreundlichen Abspielumgebung. Wenn Sie an verschiedenen Computern arbeiten, oder nicht den ganzen Kurs auf einmal herunterladen möchten, loggen Sie sich auf dieser Seite ein, um alle Videos des Trainings als Streaming-Video anzusehen.

Wir hoffen, dass Sie viel Freude und Erfolg mit diesem Video-Training haben werden. Falls Sie irgendwelche Fragen haben, zögern Sie nicht uns zu kontaktieren!