SQL Summe von Gruppen berechnen und daraus MAX-Wert anzeigen (alle gleichen)

[palaber]

Hallo zusammen,

ich möchte den Artikel ausgeben, der die maximale Gesamtmenge an Bestellungen besitzt.
Dies geht ja ganz gut so:

SELECT bp.Artikel_Nr, SUM(bp.Bestellmenge)
FROM bestellpositionen AS bp
GROUP BY bp.Artikel_Nr
ORDER BY SUM(bp.Bestellmenge) DESC
LIMIT 1

Jetzt habe ich aber das Problem, dass (im extremsten Fall) alle Artikel gleich oft bestellt wurden.
Also sollte nicht nur eine Zeile ausgegeben werden. Wie bekomme ich das hin?
Soetwas wie HAVING MAX(SUM(...)) wäre schön. Geht das? Ich bekomme es nicht hin.

Folgendes habe ich probiert:

SELECT a.Artikel_Nr, a.Bezeichnung
FROM artikel AS a
WHERE a.Artikel_Nr IN(SELECT Artikel_Nr
                      FROM (SELECT artikel_nr, sum(bestellmenge) as summe
                            FROM bestellpositionen
                            GROUP BY artikel_nr) AS v
                      WHERE summe = (SELECT MAX(summe)
                                      FROM v));

Das Problem ist aber, dass bei "FROM v" die Tabelle nicht bekannt ist.
Erstelle ich zunächst eine View und sagen dann FROM view_name
geht es. Aber insgesamt finde ich das ganze dann etwas umständlich:

CREATE VIEW v_artikel_anzahl AS
SELECT artikel_nr, SUM(bestellmenge) AS summe
FROM bestellpositionen
GROUP BY artikel_nr;

SELECT a.Artikel_Nr, a.Bezeichnung
FROM artikel AS a
WHERE a.Artikel_Nr IN(SELECT Artikel_Nr
                      FROM v_artikel_anzahl
                      WHERE summe = (SELECT MAX(summe)
                                     FROM v_artikel_anzahl));

Wenn es denn die zweite Lösung sein muss: Gibt es eine Möglichkeite ohne View?

Danke schon mal!

 

[d2boxSteve]

Nachdem du nicht schreibst um welche Datenbank es geht formuliere ich es allgemein:

Ich würde keinen View machen sondern den "select irgendwas von y into temporary table x" in eine temporäre Tabelle im RAM schreiben, daraus kannst du dann selektieren. Am Ende ein "drop temporary table x" um aufzuräumen.
Das wären dann 3 Befehle nacheinander, zuerst der Select into tmp_table, select from tmp_table und der drop tmp_table.

 

[RalphS]

DBMS?
Eigentlich sollte FROM keine Tabelle, sondern einen Tabellenausdruck erwarten. Eine Subquery zum Beispiel.
Note, dies noch unbeachtet der Frage, ob es auch “besser“ gehen kann.
(Bei subqueries Korrelation vermeiden.)

= EDIT =
Nach nochmal drübergucken:
- Was @palaber formuliert in seinem Eingangspost.. ist mit ein wenig Interpretation doch eigentlich Folgendes:
"Ich möchte ein Ranking haben über alle Artikel gemäß maximaler Bestellmenge".

Dafür gibt es Rankingfunktionen. Die kann man dann mit WHERE limitieren auf "Rank kleiner oder gleich n" - durchaus auch "Rank ist exakt 1".

 

[Hancock]

Ne Idee, mach nen join mit ner Tabelle mit einem Eintrag (dem maximum der Menge) und filter danach das Ergebnis auf exakt diesen Wert (nach dem group, also mit having).

select b.Artikel_Nr,sum(b.Bestellmenge) as sum_menge from bestellpositionen as b 
inner join 
(select max(mengen) as Bestellmengefrom (
      select sum(Bestellmenge) as mengen from bestellpositionen group by nr
) as m
)
group by b.nr
having sum_menge=max_menge

Code natürlich komplett ungetestet.

BTW: Warum nen join: Der kann die rechte Seite einmal evaluieren und dann das Ergebnis als "Konstante" weiterverwenden, subqueries können mglw. sonst für jede Zeile des Ergebnis evaluiert werden.

 

[RalphS]

BTW: Warum nen join

und doch machst du genau das in dem Join, nämlich eine zweifach verschachtelte subquery, bei der die zweite Ebene mit der ersten korreliert 😉

Dennoch, danke für das Beispiel. Ich denke zwar, daß es unnötig komplex ist... aber es zeigt sehr schön auf, daß table <> table_expr ist.

 

[Hancock]

Na ja, der rechte Teil des Joins geht einmal über die Tabelle drüber (im schlimmsten Fall zwei mal, falls es die DB nicht hinbekommt), ohne join kann es sein, dass die "WHERE IN" clause n mal ausgeführt wird (für jeden Eintrag in der Tabelle).

Klar, table<>table_expr stimmt, manchmal wird's trotzdem haarig (und braucht ne Menge "AS").

 

[RalphS]

Ich denke halt, es ist zielführender und vor allem auch performanter (und kürzer) wenn man statt GROUP BY eine Rankingfunktion verwendet.

Denn deren Aufgabe ist es, eine Ordnung auf den Daten zu erstellen und zu standardisieren (letztere Eigenschaft brauchen wir hier nicht; es wäre aber trivial, "meiste Gesamtmenge" in "die häufigsten n" zu transformieren).

Ich hab jetzt definitiv nicht die Syntax für alle möglichen Rankingfunktionen und -Optionen über alle möglichen DBMS im Kopf.... bin aber bei der Nachrecherche über diesen Eintrag
gestolpert und bilde mir ein, daß dieses Beispiel die Problematik recht gut abbildet, deswegen spar ich mir an dieser Stelle einen eigenen Entwurf.

Natürlich muß man die Syntax der Ranking- und damit verbunden auch der Windowfunktionen ein bißchen verinnerlichen, damit man versteht, welche Parametrisierung für welche Änderungen sorgt. Hier helfen die Handbücher der verschiedenen DBMS.
Dann baut man ganz simple Abfragen mit SELECT x FROM y WHERE z zusammen, hat nur seine Rankfunktion als Spalte in SELECT und weiß sofort, was die ersten, oder letzten, n Positionen gemäß einer gewünschten Reihenfolge sein sollen.


Übrigens,

(select max(mengen) as Bestellmenge from ( select sum(Bestellmenge) as mengen ... ))

ist imo etwas haarig (Hervorhebung meine) weil es bestellpositionen.bestellmenge physikalisch schon gibt, dieser Bezeichner mit Pech mit dem Alias kollidiert und man am Ende etwas Unerwartetes bekommt.
Daher entweder anderen Alias verwenden oder die "bestellmenge" in SUM() per Tabellenname qualifizieren, um Ambiguität zu vermeiden.
Sonst hat man mit Pech genau die angesprochene Korrelation, obwohl man die womöglich weder haben wollte noch eigentlich gebraucht hätte.

 

[AW4]

Das wäre auch mit Window Functions machbar:

select *
from (select Artikel_Nr, sum(Bestellmenge),
             rank() over (order by sum(Bestellmenge) desc) as rank
      from bestellpositionen 
      group by Artikel_Nr)
where rank = 1

Hier ist es natürlich auch möglich weitere Ränge über die Anpassung der Where-Bedingung anzuzeigen, z.B. where rank <= 3.
Die Window Function rank() vergibt die Ränge auf Basis einer Tabellenplazierung, d.h. gibt es fünf mal den Rang 2, ist der nächste Rang nicht 3, sondern 7.
Ist es gewünscht, dass die Ränge immer nur um 1 steigen und damit nicht "ausgelassen" werden, kann stattdessen einfach die Window Function dense_rank() verwendet werden.

 

[palaber]

Hui, da kam ja schon ganz schön was zusammen.
Lese mir jetzt alles in Ruhe durch, allerdings Vorab:

1. Ich arbeite mit mySql MariaDB XAMPP.
2. Und ich hab schon Jahre nichts mehr mit SQL zu tun gehabt. Und damals was es TSQL MS SQL Server.
3. Der zweite Lösungsansatz ist von einem Komulitonen. Ich selber hatte gar nicht daran gedacht, dass mehrere Artikel gleich oft bestellt werden können.

Edit:
Also dank der rank-Funktion (war mir bisher unbekannt) klappt es jetzt.
Ich habe mich ganz nah an AW4s snippet gehalten.
Musste in der FROM Klausel nur noch ein Alias einfügen.

SELECT t1.Artikel_Nr, t1.summe AS 'Gesamtsumme Bestellungen pro Artikel'
FROM (SELECT Artikel_Nr, SUM(Bestellmenge) AS summe, rank() OVER (ORDER BY SUM(Bestellmenge) DESC) AS rank
      FROM bestellpositionen
      GROUP BY Artikel_Nr) AS t1
WHERE t1.rank = 1

So sieht es jetzt echt gut aus und schön übersichtlich!