2 x sum() bei 2 Joins

Computer: Software & Programmierung SQL & Datenbank - Programmierung
#1

Hallo:smile:

zur Vereinfachung des Problems, nehmen wir an ich habe 3 Tabellen:

  • in der ersten Tabelle sind alle Artikel aufgelistet
    ARTIKEL
    id
    …
  • in der zweiten Tabelle kommen ALLE Artikel ein- oder mehrfach vor
    TABELLE2
    artikel_id
    x
  • in der dritten Tabelle kommen EINIGE dieser Artikel ein- oder mehrfach vor
    TABELLE3
    artikel_id
    y

Jetzt soll in EINER Select-Abfrage zunächst tabelle 2 nach artikel_id gruppiert werden , wobei x gegebenfalls summiert wird (sum(x) As x),
anschließend sollen die y in Tabelle 3 genau so summiert werden, falls keine Einträge bestehen, soll y = 0 gesetzt werden.

Ist so etwas möglich? Ich grübel seit Stunden daran und finde keine Lösung.

Gruß, Andreas

#2

Hi,

so könnte das evtl. funktionieren (wobei ich beim outerjoin wieder nicht sicher bin ob links oder rechts):

SELECT a.artikel\_id, NVL(SUM(a.x),0), NVL(SUM(b.y),0)
FROM tabelle2 a
 ,tabelle3 b
WHERE b.artikel\_id(+) = a.artikel\_id
GROUP BY a.artikel\_id

andere Möglichkeit:

SELECT a.id 
 ,NVL(
 (SELECT SUM(b.x)
 FROM tabelle2 b
 WHERE b.artikel\_id = a.id),0) AS summe\_x
 ,NVL(
 (SELECT SUM(c.y)
 FROM tabelle2 c
 WHERE c.artikel\_id = a.id),0) AS summe\_y
FROM artikel a

Gruß
Andreas

[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]

#3

Nochmal huhu,

bin zu nett für diese Welt;

CREATE TABLE tst\_artikel 
(id NUMBER(9,0) NOT NULL 
,bezeichnung VARCHAR2(255)); 

CREATE TABLE tst\_tabelle2
(artikel\_id NUMBER(9,0) 
,betrag\_x NUMBER(9,2)); 

CREATE TABLE tst\_tabelle3
(artikel\_id NUMBER(9,0) 
,betrag\_y NUMBER(9,2)); 

INSERT INTO tst\_artikel VALUES
(1,'Nummer 1');
INSERT INTO tst\_artikel VALUES
(2,'Nummer 2');
INSERT INTO tst\_artikel VALUES
(3,'Nummer 3');
INSERT INTO tst\_artikel VALUES
(4,'Nummer 4');
INSERT INTO tst\_artikel VALUES
(5,'Nummer 5');
INSERT INTO tst\_artikel VALUES
(6,'Nummer 6');

INSERT INTO tst\_tabelle2 VALUES
(1,15.8);
INSERT INTO tst\_tabelle2 VALUES
(1,17.8);
INSERT INTO tst\_tabelle2 VALUES
(2,20.8);
INSERT INTO tst\_tabelle2 VALUES
(2,20.7);
INSERT INTO tst\_tabelle2 VALUES
(4,15.8);
INSERT INTO tst\_tabelle2 VALUES
(4,15.8);
INSERT INTO tst\_tabelle2 VALUES
(6,15.8);
INSERT INTO tst\_tabelle2 VALUES
(6,15.8);

INSERT INTO tst\_tabelle3 VALUES
(1,99.50);
INSERT INTO tst\_tabelle3 VALUES
(1,120.50);
INSERT INTO tst\_tabelle3 VALUES
(5,99.50);
INSERT INTO tst\_tabelle3 VALUES
(5,1.50);

Nun die Abfragen:

SELECT a.artikel\_id, SUM(a.betrag\_x), SUM(b.betrag\_y)
FROM tst\_tabelle2 a
 ,tst\_tabelle3 b
WHERE b.artikel\_id(+) = a.artikel\_id
GROUP BY a.artikel\_id;


SELECT a.id
 ,bezeichnung 
 ,NVL(
 (SELECT SUM(b.betrag\_x)
 FROM tst\_tabelle2 b
 WHERE b.artikel\_id = a.id),0) AS summe\_x
 ,NVL(
 (SELECT SUM(c.betrag\_y)
 FROM tst\_tabelle3 c
 WHERE c.artikel\_id = a.id),0) AS summe\_y
FROM tst\_artikel a; 

SELECT a.bezeichnung
 ,NVL(SUM(c.betrag\_y),0)
 ,NVL(SUM(b.betrag\_x),0)
FROM tst\_artikel a
 ,tst\_tabelle2 b
 ,tst\_tabelle3 c
WHERE a.id = b.artikel\_id (+)
AND a.id = c.artikel\_id (+)
GROUP BY a.bezeichnung;

Wahrscheinlich ist die letzte die, die Du haben möchtest.

Gruß
Andreas

1 Like
#4

Hi Andreas!

Danke für deine Antwort. Ich wußte gar nicht, daß man auch mehrere Abfragen ineinander verschachteln kann wie in Beispiel 2.

Ich werde es gleich probieren, anzuwenden, würde aber gerne noch ein paar Sachen verstehen, die ich nicht kenne:

  • was bedeutet im ersten Beispiel das Pluszeichen in der WHERE-Klausel?
  • was genau bedeutet ‚FROM tabelle2 a , tabelle3 b‘ ? Du greifst ja in den SELECT-Statements auf ‚a‘ und ‚b‘ zurück, ist es also eine Art „Tabelle2 alias a“?

Dieses NVL-Ding wandelt ja anscheinend NULL werte in 0 um, das kannte ich auch noch nicht, sehr praktisch!

Vielen Dank,
Andreas

[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]

#5

Hi,

das „plus-Ding“ ist ein Outerjoin. Dieser wird bei Verknüpfungen die „optional“ sind angewendet.

Normalerweise bei Verküpfungen bring der Select ja nur Datensätze zurück, die in allen beteiligten, verknüpften Tabellen sind. Beim Outerjoin gibt er alle Daten der einen Tabelle zurück und die dazu passenden der anderen.

Ist sehr gewöhnungsbedürftig und ich arbeite lieber mit Sub-Selects.

Ich arbeite übrigens auf Oracle, bei MySql etc. ist der Syntax ggfls. anders.

Gruß
Andreas

#6

Hallo Andreas und Andreas L.

Nun die Abfragen:

SELECT a.artikel_id, SUM(a.betrag_x), SUM(b.betrag_y)
FROM tst_tabelle2 a
,tst_tabelle3 b
WHERE b.artikel_id(+) = a.artikel_id
GROUP BY a.artikel_id;

SELECT a.id
,bezeichnung
,NVL(
(SELECT SUM(b.betrag_x)
FROM tst_tabelle2 b
WHERE b.artikel_id = a.id),0) AS summe_x
,NVL(
(SELECT SUM(c.betrag_y)
FROM tst_tabelle3 c
WHERE c.artikel_id = a.id),0) AS summe_y
FROM tst_artikel a;

SELECT a.bezeichnung
,NVL(SUM(c.betrag_y),0)
,NVL(SUM(b.betrag_x),0)
FROM tst_artikel a
,tst_tabelle2 b
,tst_tabelle3 c
WHERE a.id = b.artikel_id (+)
AND a.id = c.artikel_id (+)
GROUP BY a.bezeichnung;

Ist das nicht etwas zu Oracle-spezifisch, mit + und NVL?

Schlage eine andere Variante vor:

  1. Abfrage für die Artikelsummierung in Tabelle 2:

SELECT artikel_id, sum(x) FROM tab2
GROUP BY artikel_id
HAVING SUM(x) > 0;

  1. Abfrage um in Tabelle 3 y=0 zu setzen, wenn noch keine Einträge bestehen:

INSERT INTO tab3 (artikel_id, y)
SELECT artikel_id, 0 FROM artikel
WHERE artikel_id NOT IN
(SELECT artikel_id FROM tab3)

und 3. Abfrage wie die erste, nur statt tab2 -> tab3 und statt y -> y

SELECT artikel_id, sum(y) FROM tab2
GROUP BY artikel_id
HAVING SUM(y) > 0

wobei man Abfrage 3 auch gleich nach der ersten Abfrage machen könnte, käme aufs gleiche raus.

Aber vielleicht sollte man überlegen, ob die Schwierigkeiten nicht daher kommen, dass die Tabellen nicht oder schlecht normalisiert wurden. In der jetzigen (vereinfachten) Form ist es schwierig einzusehen, warum zwei eigentlich strukturell identische Tabellen (Tabelle 2 und Tabelle 3) existieren sollten.

Mit freundlichen Grüssen
Klaus Bernstein

#7

Hi,

Ist das nicht etwas zu Oracle-spezifisch, mit + und NVL?

Klar, aber (nur) da kenne ich mich aus;

Schlage eine andere Variante vor:

  1. Abfrage für die Artikelsummierung in Tabelle 2:

SELECT artikel_id, sum(x) FROM tab2
GROUP BY artikel_id
HAVING SUM(x) > 0;

  1. Abfrage um in Tabelle 3 y=0 zu setzen, wenn noch keine
    Einträge bestehen:

INSERT INTO tab3 (artikel_id, y)
SELECT artikel_id, 0 FROM artikel
WHERE artikel_id NOT IN
(SELECT artikel_id FROM tab3)

und 3. Abfrage wie die erste, nur statt tab2 -> tab3 und
statt y -> y

SELECT artikel_id, sum(y) FROM tab2
GROUP BY artikel_id
HAVING SUM(y) > 0

wobei man Abfrage 3 auch gleich nach der ersten Abfrage machen
könnte, käme aufs gleiche raus.

Würde Voraussetzen, das Dir der (fachliche/logische) Hintergrund bekannt ist.

Hinderlich wäre das z.B. bei einem Großhändler, welcher die Tabellen „Artikel“, „Einkauf“ (hier tab1) und „Verkauf“ (hier Tab2) hat.

Aber vielleicht sollte man überlegen, ob die Schwierigkeiten
nicht daher kommen, dass die Tabellen nicht oder schlecht
normalisiert wurden. In der jetzigen (vereinfachten) Form ist
es schwierig einzusehen, warum zwei eigentlich strukturell
identische Tabellen (Tabelle 2 und Tabelle 3) existieren
sollten.

Würde auch voraussetzen, dass die Fachlichkeit bekannt ist. Zumal eine Relationale Datenbank auch zu Ziel hat möglichst wenige Daten und Redundanzen zu speichern.

Liebe Grüße aus dem schönen Saarland
Andreas

#8

Hallo,

danke für den Versuch einer Orakel-freien Version (ich probier zur Zeit noch die Version von Andreas zu übersetzen:smile:

Aber das ist nicht ganz was ich brache, denn es muß alles in einer Abfrage passieren. Was ich am Schluß brauche sind alle Ergebnisse mit SUM(x) > 0 , absteigend sortiert nach SUM(y).

Zum Inhaltlichen Hintergrund:
Tabelle1 - Artikel ist klar
Tabelle2 - Warenbestand
Tabelle3 - verkaufte Artikel (mit relation zur jeweiligen Rechnungsnummer usw.)

Das Ganze ist noch viel Umfangreicher, aber der Rest spielt hier keine Rolle. Ich denke nicht, das man es besser lösen kann, die Datenbank ist in Normalform und ohne Redundanz. Das macht natürlich die Anfragen kompliziert. Aber ich laß mich auch gerne eines besseren Belehren:smile:

Gruß, Andreas

[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]

#9

Hallo Andreas,

Ist das nicht etwas zu Oracle-spezifisch, mit + und NVL?

Klar, aber (nur) da kenne ich mich aus;

Sollte ja auch keine persönliche Kritik sein, aber ich bevorzuge Lösungen, die soweit wie möglich implementationsunabhängig sind. Besonders dann, wenn nicht bekannt ist, was für eine SQL-Variante der Fragende benutzt.

wobei man Abfrage 3 auch gleich nach der ersten Abfrage machen
könnte, käme aufs gleiche raus.

Würde Voraussetzen, das Dir der (fachliche/logische)
Hintergrund bekannt ist.

Hinderlich wäre das z.B. bei einem Großhändler, welcher die
Tabellen „Artikel“, „Einkauf“ (hier tab1) und „Verkauf“ (hier
Tab2) hat.

Wo Du Recht hast, hast Du Recht. Aber die Anzahl der verkauften Artikel (um bei deinem Beispiel zu bleiben) bleibt sich gleich, ob ich sie vor oder nach dem Einfügen von Nullen (was in Abfrage 3 geschieht) abfrage. Insofern erschliesst sich der Sinn dieses Einfügens nur schwer.

Aber vielleicht sollte man überlegen, ob die Schwierigkeiten
nicht daher kommen, dass die Tabellen nicht oder schlecht
normalisiert wurden. In der jetzigen (vereinfachten) Form ist
es schwierig einzusehen, warum zwei eigentlich strukturell
identische Tabellen (Tabelle 2 und Tabelle 3) existieren
sollten.

Würde auch voraussetzen, dass die Fachlichkeit bekannt ist.

Da rennst Du offene Türen ein. Vielleicht habe ich mich nur zu knapp oder zu dunkel ausgedrückt. Meine Bemerkung

in der jetzigen (vereinfachten) Form

zielte genau darauf ab, dass es hilfreich wäre, etwas mehr Informationen (oder mehr Fachlichkeit) zu bekommen.

Zumal eine Relationale Datenbank auch zu Ziel hat möglichst
wenige Daten und Redundanzen zu speichern.

Nun ja, ich weiss:
„Habe nun, ach! Programmierung,
Netzwerke und Compiler,
Und leider auch Coddsche Normalformen
Durchaus studiert, mit heißem Bemühn.“

Liebe Grüße aus dem schönen Saarland
Andreas

Ebenfalls liebe Grüsse aus dem ebenfalls schönen Nordrhein-Westfalen
Klaus Bernstein

#10

KLAPPT:smile:
Hi Andreas L.,

danke nochmal für deine ausführlichen Beiträge, nach 24 Stunden hab ich jetzt endlich die Lösung. Es war deine 2.Version. Hier in der Version für mysql, falls es sonst noch jemanden interessiert:smile:

Der Trick ist: Aufgrund eines Bugs(?) funktioniert es in phpmyadmin nicht, es gibt dort eine Fehlermeldung. Wenn man direkt über mysql.exe geht, klappt es aber.

Vielen Dank nochmal, auf diese Verschachtelung wäre ich alleine nie gekommen!!
Andreas

SELECT tst_artikel.id
,bezeichnung
,IFNULL((
(SELECT SUM(tst_tabelle2.betrag_x)
FROM tst_tabelle2
WHERE tst_tabelle2.artikel_id = tst_artikel.id),0) AS summe_x
,IFNULL((
(SELECT SUM(tst_tabelle3.betrag_y)
FROM tst_tabelle3
WHERE tst.tabelle2.artikel_id = tst_artikel.id),0) AS summe_y
FROM tst_artikel;