Summe aus Summe

[DerEsWissenWill]

Hallo Forum,

ich habe so eine Abfrage:

SELECT 
t1.number - SUM(t2.number) as return
FROM t1
LEFT JOIN t2
USING (fieldname)
GROUP BY fieldname
HAVING return>0 

Die geht auch wunderbar. Ich hätte allerdings gern mit einer Abfrage die Summe von return, also SUM(SUM()), das scheint aber nicht zu gehen. Ich bekomme jedenfalls immer eine Fehlermeldung, dass GROUP BY dann nicht stimmt. Da gibt es doch sicher ne Möglichkeit, oder? Danke Euch!

Schönen Abend, Martin

[AmicaNoctis]

Hallo,

da du deine Tabellen nicht näher spezifiziert hast (Spaltennamen), kann man nicht genauer erahnen, was du konkret machen möchtest.

Eventuell hilft dir "group by ... with rollup" schon weiter?

Generell ist die Addition kommutativ, daher kannst du eine Summe gruppieren wie du willst, es kommt immer das gleiche raus.

Falls das nicht hilft, bitte nochmal genauer werden.

Gruß,

Amica

[DerEsWissenWill]

Tabelle

Oh, ich dachte das reicht. Die Tabelle sieht halt grob so aus:

Tabelle 1:
t1.id
t1.number

Tabelle 2:
t2.id
ts.number

Tabelle 2 kann mehrere Datensätze enthalten, die über id Tabelle 1 zugeordnet sind. Konkret will ich also wissen, wieviel t1.number - summe(t2.number) noch offen ist. Aber halt wieder in Summe, also Quasi Summe(t1.number - Summe(t2.number)). Ich habe das zwischenzeitig mit einem Subselect gelöst, dachte aber es gibt noch eine Lösung ohne:

SELECT SUM(tempfield) AS return
FROM
(
    SELECT
    t1.number - SUM(t2.number) as tempfield
    FROM
    t1
    LEFT JOIN
    t2
    USING (id)
    GROUP BY t1.id
) as subselect; 

[AmicaNoctis]

Ok, ist es richtig, dass es zu jedem Datensatz in t1 beliebig viele datensätze in t2 geben kann?

[AmicaNoctis]

Hast du es schon mal so versucht?:

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

[DerEsWissenWill]

Deine Annahme ist richtig. In Tabelle 2 stehen (eventuell) mehrere Datensätze zu Tabelle 1.

Dein SQL Statement funktioniert, bringt aber nicht den korrekten Wert, den ich brauche. Nur zur Sicherheit nochmal. Ich brauche das Feld aus Tabelle 1 - die Summe der dazugehörigen aus Tabelle 2. Und daraus zeilenweise die Gesamtsumme.

Im Moment gehe ich t1.number - SUM(t2.number) einfach mit while durch, ich hätte das aber natürlich lieber mit einer Abfrage.

[AmicaNoctis]

bringt aber nicht den korrekten Wert, den ich brauche.

Woher weißt du das? Welcher Wert hätte rauskommen müssen und welcher Wert kam mit meiner Variante raus? Bist du sicher, dass du Null-Werte korrekt verrechnest, wenn es z. B. für einen Datensatz t1 mit number=20 gar keine Datensätze in t2 gibt, sollte doch trotzdem 20 rauskommen, weil nichts abgezogen wird, oder? Ich hab das berücksichtigt, du auch?

[DerEsWissenWill]

Einfach weil ich rechnen kann ;-) UNd bereits ein lauffähiges System habe nur halt mit unschönen Selects, weil ich einfach alle records mit while durchgehe. Ergo muss mit dem neuen Select das gleiche rauskommen, was mit dem alten rauskomme. Tuts aber nicht. Und ist sogar eine heftige Abweichung.

[AmicaNoctis]

Du bist leider nicht auf die Null-Werte eingegangen. Es gibt doch in deiner DB mit Sicherheit (sonst würdest du ja keinen left Join brauchen) Rows in t1, zu denen es keine Row in t2 gibt, richtig?

Wenn du mit deiner Variante durchgehst, erhältst du also z. B.

t1.number - sum(t2.number)
= 20 - sum(null)
= 20 - null
= null

Was machst du mit diesem null-Wert, für den ja eigentlich 20 rauskommen müsste, oder nicht?

Wenn du deine while-Schleife mal so modifizierst

t1.number - sum(ifnull(t2.number, 0))

, was kommt dann dabei raus?

[DerEsWissenWill]

Ich mache eni HAVING numbers>0, vielleicht ist das das was Dir fehlte. Allerdings muss ich dazusagen, dass bei mir niemals NULL stand, sondern immer 0. Warum kann ich Dir nicht sagen. Liegt das allein daran, dass es ein int field ist?

[AmicaNoctis]

Having hat damit genau gar nichts zu tun, weil es erst ganz zum Schluss filtert, wenn alles schon berechnet ist. Mir fehlt auch nichts, danke der Nachfrage und ich gebe es auch jetzt auf, dich höflichst darauf hinzuweisen, dass deine Variante falsch rechnet, weil ich auch rechnen kann und weiß, dass meine Abfrage richtig rechnet. Den Hinweis auf das Komutativgesetzt hatte ich schon gegeben.

Wenn du in meiner Abfrage das "Left Join" durch ein "Join" ersetzt, kommt vermutlich sogar dasselbe raus, wie bei dir, weil sie dann genau so falsch rechnen müsste, wie deine Variante.

Also viel Spaß und Tschüß.

[DerEsWissenWill]

Schon klar, danke trotzdem für den Versuch der Hilfe. Es kommt trotzdem NICHT das Falsche raus. Kann schon selbst rechnen, hatte sogar mal erfolgreich Mathe Leistungskurs ;o)

Übrigens: mit IFNULL kommt exakt das gleiche raus... Wirst Du aber wahrscheinlich nicht glauben, richtig? Ist aber so...

OHNE USING kommt was anderes raus, aber das ist ja auch Quatsch..

[DerEsWissenWill]

Noch mal ich: JOIN und LEFT JOIN macht keinen Unterschied! Hab ich auch gerade getestet...

[DerEsWissenWill]

Und noch mal ich: ich KRIEGE NIRGENDWO ein NULL, immer 0.

[AmicaNoctis]

Hallo nochmal,

das lässt mir keine Ruhe. Hier mal mein Testsetting und das was dabei rauskam:

create table t1 (
	id int unsigned not null auto_increment primary key,
	number int not null
);

create table t2 (
	id int unsigned not null,
	number int not null,
	index (id),
	foreign key (id) references t1 (id)
);



insert into t1 (number) values
(15),
(18),
(21),
(24),
(27);

insert into t2 (id, number) values
(1, 2),
(1, 4),
(1, 6),  -- 2 + 4 + 6 = 12 (von 15, 3 fehlen)

(2, 8),
(2, 10), -- 8 + 10 = 18 (von 18, 0 fehlen)

(3, 12), -- 12 (von 21, 9 fehlen)

         -- keine für id 4, 24 fehlen

(5, 14); -- 14 (von 27, 13 fehlen)

         -- es fehlen 49 gesamt

		 
		 
select *
from t1
left join t2 using id;

-- +----+--------+--------+
-- | id | number | number |
-- +----+--------+--------+
-- |  1 |     15 |      2 |
-- |  1 |     15 |      4 |
-- |  1 |     15 |      6 |
-- |  2 |     18 |      8 |
-- |  2 |     18 |     10 |
-- |  3 |     21 |     12 |
-- |  4 |     24 |   NULL |
-- |  5 |     27 |     14 |
-- +----+--------+--------+



select
	*,
	sum(t2.number),
	t1.number - sum(t2.number)
from t1
left join t2 using (id)
group by t1.id;

-- +----+--------+--------+----------------+----------------------------+
-- | id | number | number | sum(t2.number) | t1.number - sum(t2.number) |
-- +----+--------+--------+----------------+----------------------------+
-- |  1 |     15 |      2 |             12 |                          3 |
-- |  2 |     18 |      8 |             18 |                          0 |
-- |  3 |     21 |     12 |             12 |                          9 |
-- |  4 |     24 |   NULL |           NULL |                       NULL |
-- |  5 |     27 |     14 |             14 |                         13 |
-- +----+--------+--------+----------------+----------------------------+

-- Mit while durchgehen und summieren: 3 + 0 + 9 + 13 = 25 (statt 49)


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

-- +----+--------+--------+---------------------------+---------------------------------------+
-- | id | number | number | sum(ifnull(t2.number, 0)) | t1.number - sum(ifnull(t2.number, 0)) |
-- +----+--------+--------+---------------------------+---------------------------------------+
-- |  1 |     15 |      2 |                        12 |                                     3 |
-- |  2 |     18 |      8 |                        18 |                                     0 |
-- |  3 |     21 |     12 |                        12 |                                     9 |
-- |  4 |     24 |   NULL |                         0 |                                    24 |
-- |  5 |     27 |     14 |                        14 |                                    13 |
-- +----+--------+--------+---------------------------+---------------------------------------+

-- Mit while durchgehen und summieren: 3 + 0 + 9 + 24 + 13 = 49 (korrekt)



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

-- +-----------------------------------------------------+
-- | sum(distinct t1.number) - sum(ifnull(t2.number, 0)) |
-- +-----------------------------------------------------+
-- |                                                  49 |
-- +-----------------------------------------------------+

-- korrektes Ergebnis

Kannst dich ja gerne dazu äußern, wo gegebenenfalls Unterschiede zu deinem System bestehen.

Vielleicht finden wir dann raus, warum meine Variante bei dir nicht zu funktionieren scheint.

Gruß,

Amica