Select mit Summe über Tabellen mit Join

[Dumpfbacke]

Hallo Leute,
könnt Ihr mir mal helfen einen Select zu bauen
Ich habe hier zwei Tabellen.

Delphi-Quellcode:

Tabell1:

Zähler Lagerplatz
1,      Schrank 1 
2,      Schrank 2 
3,      Schrank 3 
4,      Schrank 4 
5,      Schrank 5 
6,      Schrank 6

Delphi-Quellcode:

Tabelle 2:

Artikel,      IDSchrank, Preis
Roter Kleber,  1,           15,20
Gelber Kleber, 1,            7,25
Grüner Kleber, 2,           13,14
Rosa Kleber,   4,           14,87

Als Ergebnis möchte ich die Beträge haben je Schrank. Hier ist das Problem ich möchte auch die leeren Schränke haben

Delphi-Quellcode:

Schrank 1,  22,45
Schrank 2,  13,87
Schrank 3,   0,00 -.> oder auch leer Hauptsache es ist der Schrank 3 hier vorhanden
Schrank 4,  14,87
Schrank 5,   0,00 -.> oder auch leer Hauptsache es ist der Schrank 5 hier vorhanden
Schrank 6,   0,00 -.> oder auch leer Hauptsache es ist der Schrank 6 hier vorhanden

Ich habe es im Selecht mit Full Join versucht oder Left Outer usw. es geht nicht. Die Leeren Schränke kommen nicht raus ?

Select Tabelle1.Lagerpaltz, Sum(Tabelle2.Preis)
From Tabelle1
Left Outer Join Tabelle2 on Tabelle1.Zähler = Tabelle2.IDSchrank

[mkinzler]

Left outer join

SQL-Code:

Select
  t1.Lagerplatz,
  t2.Artikel,
  t2.Preis
from
  Tabelle1 t1 left join Tabelle t2 on t2.IDSchrank = t1.ID;

[Dumpfbacke]

Ich möchte die Summe des Preises haben. mit Sum und Group by gehet es nicht wenn ein Schrank leer ist.

[Frickler]

Code:

with summen as
(
  select idschrank, sum(preis) as summe from tabelle2
  group by idschrank
)
select t1.lagerplatz, s.summe from tabelle1 t1 left join summen s
on t1.ZAEHLER = s.idschrank

[mkinzler]

Oder

SQL-Code:

Select
  t1.Lagerplatz,
  t2.Artikel,
  sum( coalesce(t2.Preis, 0) as Preis)
from
  Tabelle1 t1 left join Tabelle t2 on t2.IDSchrank = t1.ID
group by
  t1.Lagerplatz, t2.Artikel;

[jobo]

Left Join ist schon richtig, wenn man davon ausgehen muss, dass nicht alle Lagerplätze befüllt sind.

Wichtig ist für die Ausgabe von "Leerwerten" immer, dass sie trotzdem als Datum vorhanden sein müssen. SQL kann keine Daten anzeigen, die nicht da sind. Also muss man in diesem Fall die Lagerplatzdefinition auslesen und mit den Artikeln joinen.

Die With Lösung von Frickler macht das auch, vielleicht sogar schneller, aber ich finde es klassisch schicker und verständlicher

Code:

select lager.zähler, lager.lagerplatz, 
       sum(artikel.preis) as summe_preis
  from Tabell1 as lager left join tabelle2 as artikel
    on lager.zähler = artikel.IDSchrank
 group by lager.zähler, lager.lagerplatz

[rokli]

Noch ein Tipp von mir dazu:

Ich sorge beim DB-Entwurf bei solchen Feldern mit einem Default Wert dafür, dass gar keine NULL Werte entstehen.

Da wo das möglich ist, kann man die Probleme schon im Vorfeld minimieren.

Grüße
Rolf

[mkinzler]

@rokli die Nullwerte entstehen durch den outer Join.

[Sinspin]

Zitat:

Zitat von mkinzler (Beitrag 1458203)

Oder

SQL-Code:

Select
  t1.Lagerplatz,
  t2.Artikel,
  sum( coalesce(t2.Preis, 0) as Preis)
from
  Tabelle1 t1 left join Tabelle t2 on t2.IDSchrank = t1.ID
group by
  t1.Lagerplatz, t2.Artikel;

Hallo, ich habe keine Ahnung von FireBird. Aber in ADS oder SQLServer mache ich das so:

SQL-Code:

Select
  t1.Lagerplatz,
  t2.Artikel,
  sum( IsNull(t2.Preis, 0) as Preis)
from
  Tabelle1 t1 left join Tabelle t2 on t2.IDSchrank = t1.ID
group by
  t1.Lagerplatz, t2.Artikel;

Änderung ist hier coalesce durch IsNull ersetzt zu haben. Das hat bei ADS und SQLServer zur Folge dass auch dann summiert wird wenn in einem der Datensätze NULL für Preis steht.
-> Wobei ich mich natürlich hüte mit deutschen Feldnamen zu arbeiten. :lol:

[Dumpfbacke]

Hallo Leute,
hier mal das Ergebnis von meinen Testes welche ich gerade eben mal gemacht habe.

@Frickler
Dein Weg funktioniert. Es dauert etwas bis das Ergbnis kommt.

@mkinzler
Der Weg über coalesce funktioniert leider nicht. Es kommen die freien Lagerplätze nicht mit raus.

@jobo
SO hatte ich auch mal angefangen. Das geht leider nicht. Es kommen hier nicht die Leere Lagerplätze mit raus.

@Sinspin
isNULL kennt der Firebird leider nicht. In meinen Feld sind keie Umlaute dir. Ich habe es hur zu besseren lebarkeit so eingestellt. Das mit dem Umlauten traue ich micht auch nicht.


Somit werde ich mal den Weg von Frickler benutzen auch wenn es etwas länger dauert bis das ergebnis kommt. Danke alle alle für Eure Hilfe hier. Mal sehen eventuell hat ja noch jemadn einen Tip für mich der schneller geht.


Tanja

[IBExpert]

Select
t1.Lagerplatz,
(select sum(t2.Preis) from tabelle2 t2 where t2.IDSchrank = t1.ID) as Preis
from
Tabelle1 t1

[jobo]

Zitat:

Zitat von Dumpfbacke (Beitrag 1458224)

@jobo
SO hatte ich auch mal angefangen. Das geht leider nicht. Es kommen hier nicht die Leere Lagerplätze mit raus.

Interessant! Wenn das von Frickler geht, sollte meins oder das von mkinzler auch klappen. Vielleicht hast Du dieses Beispiel(?) zu sehr vereinfacht und bei der Übernahme in Dein System etwas übersehen?
Ein klassisches Problem bei Outer Joins ist das Ergänzen von Kriterien im WHERE auf der Outer Seite. Damit zerstört man den Outer Charakter des Joins und erhält das Ergebnis eines Full Joins (also keine Leerwerte).

Vielleicht ist ja das Statement von IBExpert schneller. Das hängt aber durchaus mit Indizierung und Mengenverhältnissen der Tabellen zusammen. Wenn Du mehr verrätst, kann man das sicher eher prognostizieren.

Vielleicht hilft auch die Ausgabe des Query Plans beim Tuning.

[MyRealName]

Ich würde mal etwas anderes vorschlagen

Man macht erstmal ein SQL, welches davon ausgeht, dass in keinem Lagerplatz etwas ist

Code:

SELECT 0 AS Total, Zähler AS Id FROM Tabelle1

Dazu ein SQL, welches zählt, was alles da ist

Code:

SELECT SUM(Preis) AS Total, IdSchrank AS Id FROM Tabelle2 GROUP BY IdSchrank

Diese beiden SQLs mit einem UNION zusammenführen und davon dann ein SUM bilden und dann noch den Lagerplatznamen holen über ein JOIN

Code:

SELECT SUM(A.Total) AS Total, A.Id, C.Lagerplatz
FROM ( 
      SELECT 0 AS Total, Zähler AS Id FROM Tabelle1
      UNION
      SELECT SUM(Preis) AS Total, IdSchrank AS Id FROM Tabelle2 GROUP BY IdSchrank
     ) A
LEFT JOIN Tabelle1 C ON (A.Id=C.id)
GROUP BY A.Id, C.Lagerplatz

Viel Spass

[p80286]

Gute Idee. Eine ähnliche Möglichkeit wäre:

SQL-Code:

SELECT max(A.Total) AS Total, A.Id, C.Lagerplatz
FROM ( 
      SELECT 0 AS Total, Zähler AS Id FROM Tabelle1
      UNION
      SELECT SUM(Preis) AS Total, IdSchrank AS Id FROM Tabelle2 GROUP BY IdSchrank
     ) A
LEFT JOIN Tabelle1 C ON (A.Id=C.id)
GROUP BY A.Id, C.Lagerplatz

Gruß
K-H

[Frickler]

Zitat:

Zitat von IBExpert (Beitrag 1458226)

Select
t1.Lagerplatz,
(select sum(t2.Preis) from tabelle2 t2 where t2.IDSchrank = t1.ID) as Preis
from
Tabelle1 t1

Bingo! Auch ohne Index die schnellste Lösung (wenn man die Statistik betrachtet).

...und meine war die langsamste :shock:

(aber alle funktionieren)

@SinSpin: "IsNull", "IfNull" und "Coalesce" hat jeweils die gleiche Funktion. "Coalesce" ist aber SQL-Standard, die anderen beiden sind herstellerspezifisch.

[jobo]

Zitat:

Zitat von Frickler (Beitrag 1458261)

Zitat:

Zitat von IBExpert (Beitrag 1458226)

Select
t1.Lagerplatz,
(select sum(t2.Preis) from tabelle2 t2 where t2.IDSchrank = t1.ID) as Preis
from
Tabelle1 t1

Bingo! Auch ohne Index die schnellste Lösung

Also IBExpert ist natürlich Experte, aber so pauschal wäre ich da vorsichtig.
Es kommt m.E. sehr auf das Mengenverhältnis von T1 zu T2 an. Je nach absoluten Zahlen dürfte auch ein Index einen großen Unterschied machen.
Man kann vielleicht aus Anwendungssicht hier sagen, ok, soviel Schränke hat kein Mensch, erst Recht nicht so viel Kleber. Also alles relativ...


P.S.: das eigentlich Interessante ist doch, dass bei Tanja nur die Variante von Frickler läuft.

[Frickler]

Zitat:

Zitat von jobo (Beitrag 1458263)

Also IBExpert ist natürlich Experte, aber so pauschal wäre ich da vorsichtig.
Es kommt m.E. sehr auf das Mengenverhältnis von T1 zu T2 an. Je nach absoluten Zahlen dürfte auch ein Index einen großen Unterschied machen.
Man kann vielleicht aus Anwendungssicht hier sagen, ok, soviel Schränke hat kein Mensch, erst Recht nicht so viel Kleber. Also alles relativ...

P.S.: das eigentlich Interessante ist doch, dass bei Tanja nur die Variante von Frickler läuft.

Ich hatte einfach die beiden Tabellen nachgebaut und direkt getestet. Vermutlich sehen die "eigentlichen Tabellen" ganz anders aus...

P.S.: laut FB-Forum wird eine CTE intern wie eine derived table ausgeführt. Ich hab das dann mal umgestellt:

Code:

select t1.lagerplatz, s.summe
from tabelle1 t1
left join (select idschrank, sum(preis) as summe
           from tabelle2
           group by idschrank) s
on t1.ZAEHLER = s.idschrank

...und auch das ist viel schneller als die CTE.

[IBExpert]

Zitat:

Zitat von jobo (Beitrag 1458263)

Es kommt m.E. sehr auf das Mengenverhältnis von T1 zu T2 an.

das stimmt, udn es ist auch wichtig darauf zu achten, aber das es in der detailtabelle einen index zum master geben sollte, müsste jedem klar sein und mit einem FK hättest du den bei Firebird automatisch. Das schöne an so einem Verfahren ist, das du relativ genau weisst, wieviele operationen es gibt, nämlich eine für die gesamtdatenmenge (also der Schränke), die du dann aber beliebig einschränken kannst, durch weitere where Bedingungen usw., und dann für jeden dort noch vorhanden Datensatz wird der Subselect aufgerufen. Das kann je nach datenmenge langsam oder eben sauschnell sein, aber nie wirklich langsam, weil der optimierer sich da vielelicht im kreis dreht

[Dumpfbacke]

Danke an alle die mir hier geholfen haben. Das Problem ist nun beseitigt worden. Der Secet von @IBExpert macht genau das was er soll und das ganze auch noch super, super schnell.

Ach so falles es jemand hier interesier. Die Mengen von T1 sind kein und die Mengen von T2 sind groß.

Tanjs

[jobo]

Zitat:

Zitat von Dumpfbacke (Beitrag 1458638)

Die Mengen von T1 sind kein und die Mengen von T2 sind groß.

Danke für das Feedback, das ergibt ein stimmiges Bild.