Summe aus Summe

[DerEsWissenWill]

Ich hab's. Missverständnis. Es gibt in jedem Fall mindestens einen Datensatz zu Tabelle 1 (hab mich wohl blöd ausgedrückt mit dem eventuell, das sollte heiußen eventuell mehrere).

Das Problem bei Deiner Variante

select sum(distinct t1.number) - sum(ifnull(t2.number, 0))
from t1
left join t2 using (id) 

ist halt, dass mir das noch immer nicht die Gesamtsumme liefert, sondern wieder mehrere Zeilen, eben 1 für jede id in Tabelle 1. Und ich will ja die Gesamtsumme der einzelnen (zeilenweisen) Teilsummen aus 1.number - t2.number.

[AmicaNoctis]

Das Problem bei Deiner Variante

select sum(distinct t1.number) - sum(ifnull(t2.number, 0))
from t1
left join t2 using (id) 

ist halt, dass mir das noch immer nicht die Gesamtsumme liefert, sondern wieder mehrere Zeilen, eben 1 für jede id in Tabelle 1. Und ich will ja die Gesamtsumme der einzelnen (zeilenweisen) Teilsummen aus 1.number - t2.number.

Nein, das liefert nur eine Zeile (siehe meinen Testcode ganz unten). Hast du es etwa mit group by abgesetzt? Da darf kein group by mehr rein, du musst es so abschicken, wie es ist.

[DerEsWissenWill]

Dann geht das Select, liefert aber anderes Ergebnis. Also, wenn ich die Ergebniszeilen aus

select
t1.number - sum(ifnull(t2.number, 0)) as return
from t1 
left join t2 using (id)
group by id
having return > 0 

addiere, kriege ich nicht das Ergebnis, das mir

select
sum(distinct t1.number) - sum(ifnull(t2.number, 0)) 
from t1 
left join t2 using (id) 

liefert.

[AmicaNoctis]

Ändert sich das Ergebnis, wenn du bei deiner Variante "group by t1.id" statt "group by id" verwendest? Hast du die Spalte t2.id wirklich mit einem Index und einem InnoDB-Fremdschlüsselconstraint versehen? Kannst du evtl. mal die relevanten Create-Table-Statements posten?

Ansonsten erläutere bitte mal, inwiefern sich dein System von meinem Testsetting unterscheidet.

[DerEsWissenWill]

Also, zu 1: Das Ergebnis ändert sich nicht. Ich verwende dennoch ein t1.id, ist ja auch richtig, oder?

Zu 2: Klar, da ist ein Index drauf. Was ist denn ein "InnoDB-Fremdschlüsselconstraint"?? Die Tabelle ist übrigens MyISAM.

Weitere Unterscheidungen wüsste ich auch nicht. Hier mal die Creates:

CREATE TABLE `orderinflow` (
    `id_orderinflow` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `salesvolume` DECIMAL(7,2) UNSIGNED NOT NULL DEFAULT '0.00',
    PRIMARY KEY (`id_orderinflow`),
    INDEX `salesvolume` (`salesvolume`)
)
COLLATE=utf8_general_ci
ENGINE=MyISAM
ROW_FORMAT=DYNAMIC

CREATE TABLE `orderinflow2invoice` (
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `id_orderinflow` INT(11) UNSIGNED NOT NULL DEFAULT '0',
    `salesvolume_invoiced` DECIMAL(7,2) UNSIGNED NOT NULL DEFAULT '0.00',
    PRIMARY KEY (`id`),
    INDEX `id_orderinflow` (`id_orderinflow`),
    INDEX `salesvolume_invoiced` (`salesvolume_invoiced`)
)
COLLATE=utf8_general_ci
ENGINE=MyISAM
ROW_FORMAT=FIXED 

Das sind jetzt nur die relavanten Felder, die anderen habe ich weggelassen.

[AmicaNoctis]

Hallo,

ich vermute, du bekommst unterschiedliche Ergebnisse, weil bei dir durch den fehlenden Constraint irgend wann mal die referentielle Integrität flöten gegangen ist. InnoDB ist die Engine, mit der man echte Fremdschlüsselbeziehungen abbilden kann, die dann auch überprüft werden.

Gruß,

Amica

[DerEsWissenWill]

Also, ich glaube nicht, dass es an der Datenbank liegt. Warum geht dann die andere Abfrage? Ich glaube eher, dass die Abfrage

select 
sum(distinct t1.number) - sum(ifnull(t2.number, 0))  
from t1  
left join t2 using (id) 

falsch ist. Schließlich dürfte das distinct.number doppelte Werte in der Spalte number rausschmeißen. Das ist aber nicht korrekt, denn dort können natürlich mehrfach gleiche Werte vorkommen. Oder sehe ich das falsch?

[DerEsWissenWill]

Ok, wenn ich es so mache, dann geht es:

select  
sum(t1.number) - sum(ifnull(t2.number, 0))   
from t1   
left join t2 using (id) 

Also ich bekomme das richtige Ergebnis. Aber ich wüsste auch gern, bei wievielen Datensätzen diese Differenz > 0 ist. Ich würde also gern sowas in dieser Art machen:

select
count(id),  
sum(t1.number) - sum(ifnull(t2.number, 0)) as result
from t1   
left join t2 using (id)
HAVING result > 0 

dann liefert mir aber das count(id) die Gesamtzahl der Datensätze. Also brauche ich wieder GROUP BY, oder? Und dann bin ich wieder da wo ich am Anfang herkam. Ich kriege dann nämlich wieder mehrere Zeilen und nicht nur eine mit der Gesamtsume. Kann ich bei dem count() irgendwas anderes nutzen?

[DerEsWissenWill]

Und noch was. Warum macht denn das

select   
sum(t1.number) - sum(ifnull(t2.number, 0))    
from t1    
left join t2 using (id) 

und das

select   
t1.number - sum(ifnull(t2.number, 0))    
from t1    
left join t2 using (id) 

ein anderes Ergebnis? t1.number ist doch immer das gleiche Feld und die Summe aus dem einen Feld gibt doch das gleiche Ergebnis?!?

[AmicaNoctis]

Das erste Statement kann nur Blödsinn liefern, weil es die Summe aller im Kreuzprodukt vorhandener Rows von t1 bildet, also auch doppelte und dreifache mit summiert.

select   
sum([B]distinct[/B] t1.number) - sum(ifnull(t2.number, 0))    
from t1    
left join t2 using (id)

dagegen summiert jede Row aus t1 nur einmal und daher gibt dieses Statement die Summe aller t1-Rows abzüglich der Summe aller t2-Rows zurück, also das was du ursprünglich mal wolltest. Du hast zwar gefordert, dass t2 dann jeweils zu t1 gehören muss, aber diese Forderung ist wegen des Kommutativgesetzes tautologisch.

Das zweite Statement liefert in genau dieser Form auch nur Blödsinn, weil es keine group-by-Klausel hat. Dadurch wird ein beliebiger Datensatz aus t1 herangezogen und von dessen Wert die Summe der number-Felder der assoziierten t2-Rows abgezogen. Mit einer "group by t1.id"-Klausel dagegen liefert das zwar deine gewünschten Zwischenergebnisse, aber für jedes t1 einzeln und nicht die Gesamtsumme, wie du ja gefordert hast. Das Statement mit group by wäre also korrekt, müsste aber mit while durchiteriert und subsummiert werden.

[AmicaNoctis]

Schließlich dürfte das distinct.number doppelte Werte in der Spalte number rausschmeißen. Das ist aber nicht korrekt, denn dort können natürlich mehrfach gleiche Werte vorkommen. Oder sehe ich das falsch?

Diesen Beitrag hatte ich erst übersehen und nur deinen letzten beantwortet. Jetzt haben wir auch den Unterschied zu meinem Testsetting gefunden: Ich hatte für t1.number immer eindeutige Testwerte, du hast echte Livedaten, wo natürlich auch zufällig mal was doppelt ist.

Dein Einwand hinsichtlich distinct ist also korrekt, da sich distinct in diesem Falle ja nur auf t1.number stürzen kann und nicht den ganzen Datensatz auf Eindeutigkeit prüfen kann.

Experimente mit

sum(distinct concat(t1.number, '@', t1.id))

liefen auf das gleiche hinaus.

Ich geb mich also geschlagen.

[DerEsWissenWill]

Ok, das heißt jetzt also, dass es tatsächlich keine Möglichkeit gibt, aus meiner funktionierenden Abfrage mit GROUP BY die Einzelsummen zu addieren UND mir zu sagen, wieviele solcher Datensätze es gibt? Ich kann mir das irgendwie gar nicht vorstellen, dass das nicht geht... Also dann nur mir Subselect?

[AmicaNoctis]

Naja, oder einfach 2 Selects:

select sum(number) from t1;

und

select sum(number) from t2;

und die Ergebnisse subtrahieren.

[DerEsWissenWill]

Alles klar, danke Dir für die Hilfe.
(auch wenn ich nicht mehr weiß als vorher ;o)

[AmicaNoctis]

Naja, du weißt jetzt immerhin, dass es nicht so ohne weiteres mit einem einzigen Select geht und ich hab gelernt, dass ich besser aufpassen muss, worauf ich DISTINCT anwende.

Gruß,

Amica