[mySQL] Summen-Problem

TSc

TSc

Legendäres Mitglied
Hi!

Ich beiss mir grade an einem Problem die Zähne aus, vielleicht hat einer von euch eine Idee.

Also, ich habe zwei Tabellen, eine mit Buchungen und eine, in der Teilsummen einer Buchung verschiedenen Zwecken zugeordnet werden.

Jetzt frage ich ab, welche Buchungen noch nicht komplett mit Teilsummen von Zwecken begründet ist.
Bei 1000 Buchungen klappt es, nur bei zwei sagt er mir das die Summe der Zweckbeträge ungleich des Betrags der Buchung ist – und gibt aber zwei identische Beträge aus!

CODE
SELECT bu.buchungsID
FROM buchung bu
LEFT JOIN zweck zw
ON bu.buchungsID = zw.buchungsID
GROUP BY bu.buchungsID
HAVING sum( zw.wert ) <> max( bu.wert )



buchungsID...wert........zweckID...wert
12.................-34.99....94............-15.00
12.................-34.99....95............-19.99

Die wert-Felder sind beide decimal(10,2) .

Hat jemand eine Idee?

Danke & Gruß,
Tom
 
Die zwei Zeilen machen mir kein Problem, das muß ja so sein. Wenn zwei Zwecke derselben Buchung zugeordnet sind, die Summe aber nicht dieselbe ist und alles zu dieser Buchung gelistet wird, dann kommen per Join zwei Zeilen raus.

Irritierender finde ich, daß -15.00 - 19.99 = -34.99 ist, die Summe der Zwecke also gleich dem Gesamtwert - damit dürfte die Buchung 12 gar kein Problem (= keine Zeile) produzieren, müßte also von der Having-Klausel ausgesiebt werden.

Nimm mal das Ergebnis der folgenden Abfrage

QUOTE SELECT bu.buchungsID, bu.wert, zw.wert
FROM buchung bu LEFT JOIN zweck zw
ON bu.buchungsID = zw.buchungsID


zur Kontrolle.
 
kommt vielleicht daher, dass er die Dezimalwerte beim Rechnen(Summieren) in eine IEEE 754 Gleitkommazahl umwandelt, und die resultierende Summe daher eine andere Präzision hat. Anders könnte ich mir das nicht erklären.

Wenn du MySQL >=5.0 verwendest, kannst du ja mal das probieren:

CODE
SELECT bu.buchungsID
FROM buchung bu
LEFT JOIN zweck zw
ON bu.buchungsID = zw.buchungsID
GROUP BY bu.buchungsID
HAVING CAST( sum( zw.wert ) as DECIMAL(10,2) ) <> max( bu.wert )
 
QUOTE (Jürgen Auer @ Do 26.03.2009, 22:43) Irritierender finde ich, daß -15.00 - 19.99 = -34.99 ist, die Summe der Zwecke also gleich dem Gesamtwert - damit dürfte die Buchung 12 gar kein Problem (= keine Zeile) produzieren, müßte also von der Having-Klausel ausgesiebt werden.



Genau das Problem meinte ich.

Das die zwei Zeilen aus dem Join resultieren war mir klar, ich meinte das ich neben Buchung 12 noch eine Buchung habe bei der das Problem auftritt.


CODE SELECT bu.buchungsID, sum( zw.wert ) , max( bu.wert )
hatte ich schon zur Kontrolle, hier kam für beide Werte die gleiche Zahl raus. Was mich dann in Zusammenhang mit dem <> - Vergleich richtig stutzig gemacht hat.


Danke für den Vorschlag Maik, ich habe auch überlegt das es in diese Richtung gehen könnte, kam aber nicht drauf wie ich das regeln kann. Ich werde deinen Vorschlag ausprobieren wenn ich heute Abend wieder zu Hause bin.
 
Ich hatte noch als Verdacht, daß es einen zusätzlichen Eintrag in der Zweck-Tabelle mit derselben BuchungsId, aber Null-Betrag geben könnte, der zu irgendeiner Pathologie führt. Deshalb die Kontrollausgabe aller Werte.

Der Vorschlag von Maik müßte an einer Stelle modifiziert werden:

QUOTE CAST(sum(zw.wert) as DECIMAL(10,2)) <> Sum(Cast(zw.wert as Decimal(10,2)))


Denn


QUOTE Cast(10.004 + 10.004 as Decimal(10,2)) = 20.01
<> 20.00 = Cast(10.004 as Decimal(10,2)) + Cast(10.004 as Decimal(10,2))


Allerdings würde ich explizit runden.
 
Hmm, du gruppierst nach Buchungen, machst aber via left join die Summen aus dem "Zweck".

Mach doch mal zum Test folgendes;
CODE GROUP BY bu.buchungsID, zw.buchungsID
 
Btw; Welche mySQL-Version und Engine verwendest du?
Habe das ganze hier (Version: 5.1.32-0.dotdeb.1) kurz nachgebaut. Dein Query liefert bei myISAM und InnoDB 0 Zeilen zurück;

Repariere sonst mal die Indexe (sofern hoffentlich vorhanden)




 
Die Serverversion ist 5.0.32 und die Tabellen laufen auf myISAM.
Auch nach einer Reperatur ändert sich nichts, leider.

Hier der Test:
CODE
SELECT bu.buchungsID, sum( zw.wert ) , max( bu.wert )
FROM hhb_buchung bu
LEFT JOIN hhb_zweck zw ON bu.buchungsID = zw.buchungsID
GROUP BY bu.buchungsID, zw.buchungsID
HAVING sum( zw.wert ) <> max( bu.wert )


buchungsID...... sum( zw.wert )......max( bu.wert )
12....................-34.99...................-34.99
500.................. 2561.78................2561.78




Bei

CODE CAST(sum(zw.wert) as DECIMAL(10,2)) <> Sum(Cast(zw.wert as Decimal(10,2)))

und

CODE CAST( sum( zw.wert ) as DECIMAL(10,2) ) <> max( bu.wert )

als HAVING sagt er mir:
QUOTE
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECIMAL(10,2)) <> Sum(Cast(zw.wert as Decimal(10,2)))


Ich schau mal ob ich finde was ihn stört...
 
Da fehlen Klammern
biggrin.gif


Kommt dabei raus, wenn man das bloß tippt und nicht testet.


Nee, sorry, fehlen doch keine Klammern - sollte schon richtig zählen.

Der nächste Kandidat ist, daß jeder Ausdruck in der Having-Klausel auch unter Group By aufgeführt werden muß.

Teste zunächst per reiner Select-Abfrage, ob Cast bzw. Cast ... as Decimal(10,2)) funktioniert.
 
Die Klammern habe ich auch zuerst gezählt.
smile.gif

Erfahrung prägt.

Es scheint der Cast selber zu sein.
Mal sehen ob ich ein Handbuch zu 5.0.32 finde.

 
Ist auch da:
http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html

CAST an sich ist auch nicht das Problem, ich hab verschiedene Datentypen probiert und es klappt. Er streikt erst wenn DECIMAL ins Spiel kommt, obwohl es laut Handbuch unterstützt wird.
blink.gif


Edit:
Tja, und das ist dann auch die Lösung:
CODE
      select
        bu.buchungsID
       from
        hhb_buchung bu
       left join
        hhb_zweck zw
       on
        bu.buchungsID = zw.buchungsID
       group by
        bu.buchungsID
       having
        cast(sum(zw.wert) as char)<> cast(max(bu.wert) as char)


Vergleichen wir halt zwei fertig berechnete Strings wenn decimal nicht funktioniert.
wink.gif

Getestet in mehreren Szenarien und funktioniert.

Vielen Dank für die Anregungen, ohne euch hätte ich das nie geschafft!
 
Dann multipliziere mal testweise mit 100 bzw. 10000, um zu sehen, welche Nachkommastellen da mitgeschleift werden.


Wenn allerdings die Online-Hilfe 5.0 Infos enthält und die CHM-Hilfe (die ja auch aus Html-Dateien generiert wird) diese Infos nicht enthält, dann sieht mir das doch noch nach einer 'Bananenimplementierung' aus (= eine, die erst reifen muß).

Auch daß das bei Alonso funktioniert, könnte ein Indiz dafür sein.
 
Ja, mit der Bananenimplementierung könntest du Recht haben.

Ich habs dann übrigens normal in der fehlerhaften Variante mit 10000000 multipliziert, aber auch da verrät er mir nicht wo er noch was mitschleift.
Vermutlich müsste ich dafür die Feldformate auf decimal(10,10) oder so anpassen damit er es rausrückt.

Na ja, so weit geht ich dann für ein Privatprojekt heute nicht mehr, erstmal funktioniert es ja jetzt.
 
Bau doch mal testweise ein "sum(zw.wert)" ins SELECT ein, damit Du siehst, was bei der Summe wirklich rauskommt. Wahrscheinlich wird das sowas wie 34.990000001 sein... Vielleicht hilft da auch so etwas wie round(sum(zw.wert),2)

Ansonsten hätte ich noch den Tipp, komplett auf Integers umzustellen (also in Cent zu rechnen). Damit vermeidest Du die ganzen Fließkomma-Rundungsprobleme; die gibt es ja immer...
 
Das war auch einer der spukigen Effekte. Ich hatte die Smme auch in der select ausgeben lassen - nach der zweiten Stelle war Schluss.
Vileicht sollte ich Mulder und Scully einschalten...
cool.gif


Aber mit den Integern hast du recht, das wäre die sauberste Lösung.
Vieleicht mache ich das mal wenn ich viel Zeit habe.
Zur Zeit tut es das noch so für unser 2-Personen-Haushaltsbuch.
wink.gif


 
Für ein Haushaltsbuch? Ja... am falschen Ende gespart. Mit SAP wäre Dir das nicht passiert ;-)

Versuch es mal mit dem round(x,2) in der Summe. Ich vermute, damit erschlägst Du das Problem für den Moment.

Viele Grüße
 
Wenn es ein Float problem ist, wird round nichts bringen. Denn das ist ganz alleine eine Sache der Computer internernen Darstellung von zahlen... Das einzige was ich mir noch Vorstellen könnte was funktionieren könnte, wäre den Rückgabewert von max() zu einem Float zu konvertieren also sowas:

CODE select
       bu.buchungsID
      from
       hhb_buchung bu
      left join
       hhb_zweck zw
      on
       bu.buchungsID = zw.buchungsID
      group by
       bu.buchungsID
      having
      ( sum(zw.wert) + 1) <> ( max(bu.wert) + 1)



oder gleich so:



CODE select
bu.buchungsID
from
hhb_buchung bu
left join
hhb_zweck zw
on
bu.buchungsID = zw.buchungsID
group by
bu.buchungsID
having
( sum(zw.wert) - max(bu.wert) ) <> 0



Der Trick dabei ist allerdings, den Optimizer von MySQL auszuhebeln... weiß nicht ob das 1.Beispiel das schafft.
 
Wie gesagt, die convertierung in ein varchar wie oben beschrieben hat das Problem gelöst.

Sauberer wäre es aber sicher wenn ich das decimal Feld der Tabelle bei Gelegenheit in Integer wandel und mit Cent arbeite.

Danke für eure Mühe!

 
Sie müssen sich einloggen oder registrieren, um hier antworten zu können.
Zurück
Oben