MySQL -> SUM und Left JOIN - Mehfachberechnung

[exo]

Hi Leute,

stehe hier leider vor nem kleinen Problem und komme einfach nicht auf den Fehler.

Es ist so, ich habe zwei Tabellen, einmal Rechnungen und einmal Zahlungen.

Eine Rechnung kann in dies mehrere Zahlungen beinhalten.

Jetzt will ich prüfen, ob die Summe der erhaltenen Zahlungen dem Rechnungsbetrag entspricht oder kleiner ist und demnach Rechnungen filtern, welche noch nicht komplett bezahlt wurden.

Jedoch habe ich das Problem, so wie es ausschaut, dass die Rechnungsbeträge mehrfach summiert werden, genau um den Faktor, wie es Zahlungen gibt.

Sprich: der Rechnungsbetrag beträgt z.B. 1000 - wenn ich dann 2x 500 als Zahlung erhalten habe, gibt mir das Query dann als Rechnungsbetrag 2000 aus anstatt 1000.

Des weiteren gibt es noch eine dritte Tabelle (Aufträge) welche natürlich auch mehrere Rechnungen erhalten kann.... schlussendlich sollen natürlich alle Rechnungen summiert werden, dann die entsprechenden Zahlungen zu den Rechnungen summiert werden und dann verglichen werden.....

Hier mein bisheriges Query:

SELECT SUM( r.betrag ) AS Rebetrag, ifnull(SUM( z.betrag ), 0) AS erhalten, auftragsID
                                FROM rechnungen AS r
                                
                                LEFT JOIN zahlungen AS z ON z.rechnungid = r.id
                                WHERE r.storno =0
                                AND NOW( ) > r.rechnungsdatum + INTERVAL 10 
                                DAY 
                                GROUP BY r.auftragsID
                                HAVING Rebetrag != erhalten

 

[Talirion]

AW: MySQL -> SUM und Left JOIN - Mehfachberechnung

SELECT SUM( r.betrag ) AS Rebetrag, ifnull(SUM( z.betrag ), 0) AS erhalten, auftragsID
                                FROM rechnungen AS r
                                
                                LEFT JOIN zahlungen AS z ON z.rechnungid = r.id
                                WHERE r.storno =0
                                AND NOW( ) > r.rechnungsdatum + INTERVAL 10 
                                DAY 
                                GROUP BY r.auftragsID
                                HAVING Rebetrag != erhalten

auf den ersten Blick ist das auch genau so da du SUM( r.betrag ) verwendest... versuch mal r.betrag oder wennd as nicht den erfolg bringt dann nim MAX( r.betrag ) ... bin zwar kein SQL meister aber das dürfte gehen

LG

 

[hub]

AW: MySQL -> SUM und Left JOIN - Mehfachberechnung

Moin moin,
es gibt dort sicher mehr Lösungsansätze, es kommt sicher auch auf den Umfang der zu verarbeitenden Daten an. Spontan fallen mir 2 Möglichkeiten ein. Man kann die Summen separat berechnen und in temporary tables speichern und dann zu prüfen. Oder man löst das Problem über Sub-Querys (taugt, so denke ich, nur zum testen).

Gruß Ulli

 

[exo]

AW: MySQL -> SUM und Left JOIN - Mehfachberechnung

Geht so auch nicht, wenn ich nur den Betrag nehme, Summiert er nicht wenn z.b. eine 2. Rechnung existiert, sondern nimmt immer nur den der Letzten.

Subquerys hatte ich auch schon überlegt, bin aber auch nicht so der Fan davon und weis nicht wie sich dies perfomance technisch auswirkt.

 

[hub]

AW: MySQL -> SUM und Left JOIN - Mehfachberechnung

Moin,

Subquerys hatte ich auch schon überlegt, bin aber auch nicht so der Fan davon und weis nicht wie sich dies perfomance technisch auswirkt.

darum der Kommentar: taugt wohl nur zum Test.
Ich denke, der schnellste und sicherste Weg ist, den Vorgang aufzusplitten, also erst Ergebnisse der einzelnen relevanten Daten einer Tabelle in temporary tables zu speichern (berechnet), dann auswerten, dann temp. tables wieder löschen.
Dann gibt es das Problem der mehrfachen Durchläufe nicht und die Geschwindigkeit wird nicht zusätzlich beeinträchtigt.

Gruß Ulli

 

[Duddle]

AW: MySQL -> SUM und Left JOIN - Mehfachberechnung

Könntest du bitte die 3 Tabellen auszugsweise hier skizzieren? Also wie sieht die Struktur aus (nur die relevanten Spalten) und wie sehen jeweils Beispiel-Einträge aus? Ideal wäre es, wenn du es in einem SQL Fiddle modellierst und uns den Link gibst, aber ich kann das notfalls auch selbst übertragen.

Edit: ach ja, bitte schreib dazu auch das erwartete Ergebnis für deine Beispiel-Datensätze.


Duddle

 

[exo]

AW: MySQL -> SUM und Left JOIN - Mehfachberechnung

Mhhh würde dir es gern per sqlfiddle rüber schicken, aber irgendwie passiert bei mir nix

Hier mal ein kleines Diagramm - vielleicht hilft es dir ja so schon weiter

Als Beispiel wäre folgendes:
AuftragsID: 53

RE1: ID: 41 | Betrag: 705
RE2: ID: 621 Betrag: 6345

Zahlungen:

Rechnungid: 41 | Betrag: 705
Rechnungid: 621 | Betrag: 6000
Rechnungid: 621 | Betrag: 345

Ergebnis meines Querys:

Rebetrag: 13395.00 | erhalten:7050.00 | auftragsID: 53

Erwarten würde ich jedoch
Rebtrag: 7050.00 | erhalten: 7050.00 | auftragsID: 53

wie man sieht, summiert er jedoch bei mehrfachen Zahlungen den gesamten Rechnungsbetrag erneut auf den Rebetrag drauf ....

 

[hub]

AW: MySQL -> SUM und Left JOIN - Mehfachberechnung

Der Fehler ist richtig. Das liegt an der Arbeitsweise von mySQL. Wenn du Tabellen mit einem Join verbindest, werden alle Kombinationen durchlaufen und damit nicht nur gruppiert, sondern alle Anweisungen jedesmal ausgeführt. So auch dein SUM(). Du kannst es nur umgehen, in dem du dafür sorgst, das es zum Berechnen jeweils nur eine Kombination gibt (ob vollständig oder nicht, siehe LEFT JOIN). Also erst die Zahlungen berechen und danach mit dem Stand der Rechnungen vergleichen.
Ich kann gerade nicht testen, aber vlt funktioniert es, wenn du

SELECT SUM( r.betrag ) AS Rebetrag ...

zu

SELECT [COLOR=red]DISTINCT[/COLOR] SUM( r.betrag ) AS Rebetrag ...

änderst. Ein Schuß ins Blaue, aber kurz genug, um es mal zu probieren.

Gruß Ulli

 

[Isometric]

AW: MySQL -> SUM und Left JOIN - Mehfachberechnung

Hier mal ein kleines Diagramm - vielleicht hilft es dir ja so schon weiter

Nicht das ihr denkt, dass ich zu eurer Diskussion etwas nützliches beitragen kann ...

Aber mit welchem Programm erstellt man denn so tolle Diagramme und was macht sqlfiddle???

 

[Duddle]

AW: MySQL -> SUM und Left JOIN - Mehfachberechnung

Zuerst holen wir uns die Summe aller Zahlungen pro unterschiedlicher Rechnung:

SELECT rechnungsid, sum(zahlung) summe
FROM zahlungen
GROUP BY rechnungsid

Jetzt joinen wir diese Zahlungen mit den eigentlichen Rechnungen, um die Zahlungen pro Auftrag summieren zu können. Die Summe aller beteiligten Zahlungen ist "bezahlt", die Summe der zu zahlenden Beträge ist "gesamtbetrag"

SELECT auftragsid, sum(summe) bezahlt, sum(betrag) gesamtbetrag
FROM rechnungen r
JOIN(
 SELECT rechnungsid, sum(zahlung) summe
 FROM zahlungen
 GROUP BY rechnungsid
) z
ON r.id = z.rechnungsid
GROUP BY auftragsid

Das war es schon. Falls du einen HTML5-Browser hast, kannst du es in diesem Fiddle direkt ausprobieren.

Die Abfrage liefert dir jetzt die nötigen Informationen, um weiter filtern zu können. Vergiss aber nicht, dass du bei Aggregatsfunktionen (SUM, AVG, usw.) mit HAVING filtern musst. WHERE würde schon vor der Zusammenfassung Zeilen rauswerfen. Also bspw.

SELECT auftragsid, sum(summe) bezahlt, sum(betrag) gesamtbetrag
FROM rechnungen r
JOIN(
 SELECT rechnungsid, sum(zahlung) summe
 FROM zahlungen
 GROUP BY rechnungsid
) z
ON r.id = z.rechnungsid
GROUP BY auftragsid
HAVING bezahlt < gesamtbetrag

Duddle

 

[exo]

AW: MySQL -> SUM und Left JOIN - Mehfachberechnung

Hallo Duddle,

vielen Dank für den Lösungsweg .... was ich mich persönlich an dieser Stelle frage ist, wo liegt der Unterschied genau von Join und Left Join... bzw. ohne SubQuery würde es an dieser Stelle wohl gar nicht gehen oder?

 

[Duddle]

AW: MySQL -> SUM und Left JOIN - Mehfachberechnung

JOIN ist bei MySQL der normale INNER JOIN. Passende Zeilen werden zusammengezogen. Sobald du ein LEFT oder RIGHT nutzt, wird es als OUTER JOIN interpretiert. Diese akzeptieren auch Zeilen, die keine passende Zeile auf der anderen Seite haben. Per Google findest du da konkretere Erklärungen und Beispiele.

Ist es für dich wichtig, ohne Sub-Querys zu arbeiten? Ausgereifte DBMS wie MySQL haben kein Problem damit und optimieren das intern von alleine.


Duddle

 

[exo]

AW: MySQL -> SUM und Left JOIN - Mehfachberechnung

Nicht das ihr denkt, dass ich zu eurer Diskussion etwas nützliches beitragen kann ...

Aber mit welchem Programm erstellt man denn so tolle Diagramme und was macht sqlfiddle???

Ich habe dafür den dbForge Query Builder benutzt (= geht aber auch per phpMyAdmin

@Duddle

Danke für die kurze Erläuterung... habe ich zwar auch schon mehrmals mit der Thematik beschäftigt, aber immer nur sehr kurz, weil die Zeit oft knapp ist.

Prinzipiell habe ich damit keinerlei Probleme - sofern die Performance damit nicht sehr leidet passt das schon - dachte eventuell nur dass es der Einfachkeit und Übersichtlichkeit auch ohne geht

 

[exo]

AW: MySQL -> SUM und Left JOIN - Mehfachberechnung

Hallo nochmals Leute,

nach längerer Beobachtung ist mir leider aufgefallen, dass die Abfrage so noch nicht ganz funktioniert.

Und zwar ist es so, dass solange keine Zahlung für eine Rechnung existiert, wird diese Rechnung gar nicht mehr berücksichtigt und nicht angezeigt. Selbst bei Prüfung durch IFNULL wird keine Rechnung ausgegeben.

Das Query sieht momentan bei mir so aus:

SELECT auftragsid as id, ifnull(SUM(summe),0) as bezahlt, ifnull(sum(r.betrag), 0) as gesamtbetrag
				FROM rechnungen r
				JOIN(
				 SELECT ifnull(SUM(betrag) ,0) as summe, rechnungid
				 FROM zahlungen
				 GROUP BY rechnungid
				) z
				ON z.rechnungid = r.id
				GROUP BY auftragsid
				HAVING bezahlt < gesamtbetrag

 

[Duddle]

AW: MySQL -> SUM und Left JOIN - Mehfachberechnung

Da kommt exakt das zum tragen, was ich vorher erklärt habe: auf der "linken" Seite steht die Tabelle rechnungen mit ihren ids. Auf der "rechten" Seite steht die erzeugte Tabelle z, die zu einer bestimmten Rechnungs-ID die Summe der erfolgten Zahlungen führt.

Was passiert also, wenn du die ids in der linken und rechten Tabelle einander zuweisen willst, damit sie korrekt "nebeneinander" stehen? Du findest exakt die Paare, die zusammen gehören. Aber was wenn es für den Tänzer keine Tanzpartnerin gibt? Dann bleibt er draussen sitzen und wird ignoriert. Es sei denn, wir erlauben explizit Einzeltänzer.

Oder im Fall von SQL: wir müssen erlauben, Datensätze ohne Partner trotzdem einzubeziehen. Im Jargon ist das ein Outer Join. Da "links" unsere Rechnungen stehen, die möglicherweise rechts nichts haben, brauchen wir einen "Left Outer Join". In MySQL geht das mit "LEFT JOIN". Wenn du so die (von mir originale) SQL-Abfrage anpasst, dann bezieht er die Beträge aus Rechnungen ein, die noch keine Zahlungen erhalten haben, d.h. der Gesamtbetrag steigt.


Duddle