MySQL SUM mit GROUP Problem

[mixmastertobsi]

Hallo,

ich habe folgende Abfrage und möchte nun als Ergbenis noch eine Komplettsumme. Wie ist das möglich?

SELECT SUM(artikel_attribut_lager.menge)*ek FROM artikel_attribut
LEFT JOIN artikel_attribut_lager ON artikel_attribut_lager.atrnr=artikel_attribut.atrnr
GROUP BY artikel_attribut.atrnr

Momentan ist es so, dass jede atrnr in einer eigenen Zeile steht. Nun möchte ich aber, dass diese einzelnen Beträge addiert werden. Wenn ich hinten das Group by entferne, stimmt das Ergebniss nicht. Ich vermute hier, dass es an dem LEFT JOIN liegt, weil es hier die atrnr mehrfach gibt.

Grundsätzlich könnte ich das Ergebniss nun in PHP ausgeben lassen und dann dort addieren, allerdings ist dies wohl nicht die elegante Lösung...

Wie wäre die Abfrage korrekt?

[dedlfix]

Tach!

ich habe folgende Abfrage und möchte nun als Ergbenis noch eine Komplettsumme. Wie ist das möglich?

SELECT SUM(artikel_attribut_lager.menge)*ek FROM artikel_attribut
LEFT JOIN artikel_attribut_lager ON artikel_attribut_lager.atrnr=artikel_attribut.atrnr
GROUP BY artikel_attribut.atrnr

Momentan ist es so, dass jede atrnr in einer eigenen Zeile steht. Nun möchte ich aber, dass diese einzelnen Beträge addiert werden.

Es gibt den Group-By-Modifizierer WITH ROLLUP.

Wenn ich hinten das Group by entferne, stimmt das Ergebniss nicht. Ich vermute hier, dass es an dem LEFT JOIN liegt, weil es hier die atrnr mehrfach gibt.

Lass die Aggregatfunktion und das Gruppieren zur Ursachenanalyse weg und schau dir an, ob die Zeilen in der Ergebnismenge diejenigen sind, die du erwartest.

Meine persönliche Vorliebe wäre auch, den Join durch eine Correlated Subquery auszutauschen. Du möchtest ja keine vereinte Datenmenge haben, sondern nur zu den Zeilen der einen Tabelle einen korrelierenden Wert aus einer anderen Tabelle haben. Für mich liest sich solch ein Statement auch einfacher, weil es die Intention besser herüberbringt. Die Subquery steht an genau einem Ort und verteilt sich nicht über SELECT und FROM (und WHERE, wenn man die implizite Join-Schreibweise nimmt).

SELECT a.x aliasX, (SELECT y FROM b WHERE a.id = b.id_a) aliasY FROM a WHERE ... -- (falls benötigt)

dedlfix.

[mixmastertobsi]

Sorry - habe ich nicht verstanden. Wie würde denn meine Abfrage konkret aussehen

Beispiel Tabellen

Tabelle: artikel

ID | EK
--------
1 | 9
2 | 50

Tabelle: artikel_lager

Lagerplatz-ID | Artikel-ID | Menge
------------------------------
1 | 1 | 2
2 | 1 | 5
3 | 1 | 1
1 | 2 | 3
2 | 2 | 10

Von Aritkel 1 gibt es also 8 Artikel im Lager und von Artikel 2 ingesamt 13 Artikel im Lager. Nun muss dies mit dem EK multipliziert werden und daraus dann die Summe.

[dedlfix]

Tach!

Sorry - habe ich nicht verstanden. Wie würde denn meine Abfrage konkret aussehen

In meiner Antwort waren zwei Aspekte. Der eine und eigentlich wichtigere war, zunächst einmal auf Ursachenforschung zu gehen, warum es denn zu dem unerwarteten Ergebnis kommt. Die Abfrage sieht dazu konkret so aus, wie du sie bereits hast, aber die Summierung und die Gruppierung kommt raus. Gegebenenfalls musst du die Feldliste erweitern oder auch * selektieren, damit du sehen kannst, welche Datensätze in der Vereinigungsmenge drin sind und ob das die gewünschten sind. Oder ob nicht vielleicht ein Join-Fehler drin ist, der zu einem ungewollten kartesichen Produkt geführt hat.

Wenn du die Ursache für die falsche Summe erkannt hast, wird dir vielleicht auch einfallen, wie das Problem zu beheben ist.

---

Der zweite Teil war da, um eine generelle Alternative zu Joins aufzuzeigen, die ich für einfacher lesbar halte.

SELECT a.x aliasX, (SELECT y FROM b WHERE a.id = b.id_a) aliasY FROM a

Eine Aufgabenstellung für dieses Beispiel wäre: gibt mir Daten aus Tabelle a, repräsentiert hier durch a.x, es können aber auch beliebig viele Felder aus a gelistet werden. Und gib mir dazu genau einen Wert, der aus dem Feld y der Tabelle b ermittelt wird, der zum aktuellen Datensatz der Tabelle a passt. Die Beziehung ist dabei, dass in b das Feld id_a auf die id in a verweist. Statt dem reinen y kann auch eine Rechnung genommen werde, Hauptsache es entsteht in dieser Subquery nur ein einzelnes Ergebnis. Der Wert, der in dem Gebilde in ()-Klammern entsteht, kann auch in der Hauptquery weiterberechnet werden:

SELECT a.x aliasX, (SELECT y FROM b WHERE a.id = b.id_a) * 42 aliasY FROM a

oder

SELECT a.x aliasX, SUM((SELECT y FROM b WHERE a.id = b.id_a)) aliasY FROM a

Die doppelten Klammern sind notwendig, weil einerseits das SUM() ein Klammernpaar erfordert, andererseits Subquerys auch noch mal eigene Klammern benötigen.

Wenn du dieses Prinzip verstanden hast, sollte es eigentlich ein leichtes sein, es auf deinen Anwendungsfall anzuwenden.

Geh dabei schrittweise vor, schau dir die Zwischenergebnisse an, die die Abfragen liefern, vor allem auch bevor du Zusammenfassungen einbaust. Der Vorteil an solchen Correlated Subquerys ist auch, dass man sie recht leicht rauslösen und separat testen kann, was bei einem Join schlecht bis gar nicht geht.

dedlfix.

[mixmastertobsi]

Ich verzweifle - bei mir klappt es nicht…

Ich hatte zwar nun die Liste hinbekommen, um den Wert des aktuellen Lagerbestands zu ermitteln, doch nun wollte ich die Bestandsveränderung berücksichtigen, so dass ich zum Beispiel den Wert des Lagers zum 31.12. ausgeben kann.

Ich habe folgendes probiert, doch kommt da invalid group function

SELECT SUM((SUM(artikel_attribut_lager.menge)-SUM(artikel_attribut_history.menge))*ek) as summe...

Letztendlich soll er die Gesamtzahl von den Artikel im Lager nehmen und dann die Anzahl abziehen, die sich verändert hatte und dann soll er mit dem jeweiligen EK multiplitzieren.

[Rolf b]

Nee, SUM über SUM geht nicht, kann nicht gehen. Dafür bräuchtest Du sowas wie ein mehrstufiges GROUP BY, sonst ergibt das keinen Sinn.

Bei Dir kommt noch hinzu, dass Du die Artikel-Tabelle nun mit zwei anderen Tabellen verknüpfst, und die Gruppierung pro abhängiger Tabelle getrennt laufen muss. Das geht mit JOIN - tjaaa - zwar irgendwie schon, aber du musst dann sowas bilden wie
(Artikel JOIN Lager GROUP BY ArtNr) JOIN (Artikel JOIN History GROUP BY ArtNr)
Das ist mega-umständlich. Ob auch langsamer - keine Ahnung, das hängt davon ab wie schlau der Optimizer im SQL Server ist.

Mit Dedlfixens Subselects ist es jedenfalls einfacher. Da gruppierst Du nämlich nicht, sondern wählst pro Artikel über die WHERE Bedingung die relevanten Sätze der abhängigen Tabellen aus. Wenn Du zu einem Artikel gar keinen Lager- oder History-Satz hast, liefert der Subselect allerdings NULL, das solltest Du per COALESCE zu 0 machen. Achso - mir fällt grad auf: Dedlfix hat SUM((SELECT ...)) verwendet, ich habe (SELECT SUM(Menge)) geschrieben - die SUM((SELECT...)) Schreibweise scheint im MS SQL Server nicht zu gehen: "Cannot perform an aggregate function on an expression containing an aggregate or a subquery."

select A.ArtNr, 
       (COALESCE((SELECT SUM(Menge) FROM Lager L WHERE A.ArtNr=L.ArtNr), 0)
       - COALESCE((SELECT SUM(Menge) FROM History H WHERE A.ArtNr=H.ArtNr AND **datum passt**), 0)) * EK
FROM artikel A

Für datum passt setze die Abfrage deines Vertrauens ein.

Das passt so nicht ganz zu deinen Tables, aber das kannst Du bestimmt hinbiegen.

Rolf

[Rolf b]

Nachtrag: Habe gerade mal den Execution Plan anzeigen lassen, dieser COALESCE um den SELECT scheint ineffektiv. Zumindest auf MS SQL Server. Er führt die Abfrage auf Lager und History scheinbar zweimal aus.

Ein geschachtelter SELECT, um den COALESCE vom Subselect zu trennen, sieht besser aus:

SELECT ArtNr, ArtName, EK, Lager, Abgang, (COALESCE(Lager,0)-COALESCE(Abgang,0))*EK
FROM (SELECT A.ArtNr, ArtName, EK, 
             (SELECT SUM(Menge) FROM ArtikelLager   L WHERE A.ArtNr = L.ArtNr) Lager,
             (SELECT SUM(Menge) FROM ArtikelHistory H WHERE A.ArtNr = H.ArtNr) Abgang
      FROM Artikel A) X

Rolf

[dedlfix]

Tach!

Ich hatte zwar nun die Liste hinbekommen, um den Wert des aktuellen Lagerbestands zu ermitteln, doch nun wollte ich die Bestandsveränderung berücksichtigen, so dass ich zum Beispiel den Wert des Lagers zum 31.12. ausgeben kann.

Du hast ja nun auch noch eine weitere Datenquelle hinzugenommen und mit noch mehr Joins und noch mehr Möglichkeiten, ungewollt Datenmengen verknüpfen zu lassen, wird es nicht übersichtlicher oder einfacher.

Hast du mal probiert, wie sowas mit einem Correlated Subselect funktioniert? Stell dir mal vor, wie du die Aufgabe ohne Datenbank lösen würdest. Du bekommst eine Liste mit den Artikeln und einen Stift. Du gehst ins Lager und ermittelst den Bestand, gegebenenfalls musst du dabei summieren, wenn der Artikel an zwei Stellen liegt. Du gehst dann ins Büro und schaust in die Akten, was die bisherige Menge war. Dabei interessiert dich aber nur eine bestimmt Menge, nicht alles aus allen Jahren. Anschließend musst du noch ein bisschen rechnen und das Ergebnis in die Liste schreiben. Und das machst du für jeden Artikel.

Du hast da also ein paar Teilschritte für jede Zeile zu absolvieren. Nun stell dir vor, dass du diese Teilschritte an jemanden delegierst: "Ermittle im Lager die Anzahl vom Artikel mit der Nummer - moment, lass mich in meine Liste schauen - 4711". Und eine ähnliche Aufgabe für die zweite Zahl. Zusammenrechnen machst du selbst. Das sieht zwar etwas umständlich aus, weil du jedes Mal die Teilschritte formulierst und dein Helfer losrennt, aber du kannst auf diese Weise die Ausführung dieses konkreten Schrittes sehr einfach nachvollziehen, weil du diesen Einzelschritt im Falle eines (Problem)falles für einen einzelnen Artikel ausführen kannst statt für die gesamte Liste.

Alternativ dazu (und das wäre statt dem obigen Subselect-Ansatz der Join-Ansatz) lässt du dir eine Lagerbestandsliste und dazu noch eine Bewegungsliste als große Zahlenberge geben und verknüpfst die Werte mit deiner Artikelliste. Du kannst dann aber nicht so einfach feststellen, ob die Zahlen stimmen. Du müsstest separate Tests mit einzelnen Artikeln formulieren und kannst dir dabei nicht ganz sicher sein, dass diese Aufgabenformulierung auch zum selben Ergebnis führt, wie die Formulierung der Aufgabenstellung zur Ermittlung des Datenberges. Da hat man es doch mit der als konkrete Teilschritte formulierten Aufgabenstellung besser.

Und nun stell dir vor, wie du das mit einer Programmiersprache lösen würdest. Vermutlich auch nicht mit array_merge() oder ähnlichem Konstrukt, sondern mit einer For(each)-Schleife. Pro Artikel ermittelst du nun die Zwischenergebnisse der Teilaufgaben, alles in separaten Programmzeilen, damit der Code übersichtlich und lesbar bleibt. Zum Schluss kommt das Zusammenrechnen des Ergebnisses mit den Variablen, in denen das Zwischenergebnis liegt.

Nun schicken wir aber ungern massenhaft Einzenabfragen an das DBMS, und so kommt es, dass wir versuchen das zu einer Abfrage zusammenzufassen - und die meisten greifen zunächst zu Joins und am Ende zu Joints. Probier mal den Subselect-Ansatz. Formuliere dazu erstmal die Statements für die Teilaufgaben. Dafür sind übrigens Tools wie die MySQL Workbench sehr hilfreich. Damit kann man die Querys direkt formulieren und sofort ausführen, ohne jedes Mal Abfrageinfrastruktur drumherumzubauen, wie man das am Ende in einem Programm braucht.

Also erster Schritt: Query für die Ermittlung des Lagerbestandes für einen bestimmten Artikel. Statt "für einen bestimmten Artikel" nehmen wir für den Test erstmal eine konkrete Artikelnummer. Ergebnis ist eine einzelne Zahl, und so soll das sein. Das kann man nun mit weiteren Artikelnummern probieren und wird die richtigen Ergebnisse bekommen. Ist ja eine ziemlich einfache Abfrage. Die legen wir mal kurz zur Seite und nehmen die nächste Teilabfrage ins Visier. Und so weiter. Danach formulieren wir die Abfrage nach den Artikeln. Auch die ist einfach, SELECT id, name FROM artikel. Nun haben wir einen kleinen Stapel einzeln ausführ- und testbarer Querys und kombinieren nun die Teilaufgabenquerys mit der Artikel-Abfrage. Diese Teilaufgabenquerys fügst du zunächst wie weitere Felder in die SELECT-Klausel ein und ersetzen dabei die konkrete Artikelnummer mit einem Verweis auf das Feld der Hauptquery. Das Zwischenergebnis dieses Schrittes ist eine Liste mit Artikeln sowie den Zahlen der Teilquerys. Du kannst kontrollieren, dass dieses Zusammenfügung dieselben Werte bringt wie die zuvor separat ausgeführten Querys. Der letzte Schritt ist nun, die notwendige Zusammenrechnung dieser Teilwerte umzusetzen.

Und so kommt man Schritt für Schritt, jeden davon einzeln auf korrekte Ausführung prüfbar, zum Ergebnis anstatt einn große Join-Abfrage zu formulieren, die man bei Problemen schlecht auseinandernehmen kann. Und ein Group By kommt im besten Fall auch nicht in der Lösung vor.

Um das mal für den ersten Schritt anhand deiner Datenstruktur aufzuzeigen:

Teilaufgabe Lagerbestand für einen Artikel ermitteln:

SELECT Menge FROM artikel_lager WHERE atrnr = 1;

Falls mehrere Datensätze pro Artikel vorhanden sind, hier bereits die Aggregatfunktion anwenden

SELECT SUM(Menge) FROM artikel_lager WHERE atrnr = 1;

Im weiteren Verlauf gehe jedoch ich von nur einem Datensatz pro Artikel aus.

Hauptquery:

SELECT atrnr, ek FROM artikel;

Teilaufgabe in Hauptquery einfügen:

SELECT atrnr, ek, (SELECT Menge FROM artikel_lager WHERE atrnr = artikel.atrnr) FROM artikel;

Ausrechnen des Wertes:

SELECT atrnr, ek, (SELECT Menge FROM artikel_lager WHERE atrnr = artikel.atrnr) * ek AS wert FROM artikel;

Fertig.

Um mal die Anwendung einer zweiten Teilquery zu zeigen, ohne die konkreten Begebenheiten bei dir zu berücksichtigen. Von der aktuellen Lagermenge wird hier der Bestand vom 31.12. des Vorjahres abgezogen und von dieser Differenz der Einkaufswert ermittelt. Die gesamte Herleitung spare ich mir, aber das ist der letzte Zwischenschritt:

SELECT 
  atrnr, 
  ek, 
  (SELECT Menge FROM artikel_lager WHERE atrnr = artikel.atrnr) AS lagerbestand,
  (SELECT Menge FROM artikel_history WHERE date = '2016-12-31' AND atrnr = artikel.atrnr) AS historische_menge
FROM artikel;

und dies die fertige Query:

SELECT
  atrnr, 
  ek, 
  ( 
     (SELECT Menge FROM artikel_lager WHERE atrnr = artikel.atrnr) - 
     (SELECT Menge FROM artikel_history WHERE date = '2016-12-31' AND atrnr = artikel.atrnr) 
  ) * ek AS wert
FROM artikel;

dedlfix.

[sm]

Nullwerte machen gern Ärger beim summieren.