Summen über mehrere Detailtabellen berechnen

[Philipp Krebs]

<pre>
Hallo liebes Forum,

in meiner Anwendung (SQL-Server 2000) können einem Projekt drei verschiedene Kostenarten zugeordnet werden.
In einer Sicht sollen dann die Gesamtkosten zu jedem Projekt dargestellt werden. Nun suche ich nach einem SQL-Kommando, das dies möglichst effizient erledigt. Meine beste Lösung bisher war dieses hier:

SELECT
ProjNrID,
SUM(Kosten)
FROM
(SELECT
ProjNrID,
SUM(Kosten) AS Kosten
FROM
Proj_KKst
GROUP BY ProjNrID
UNION
SELECT
ProjNrID,
SUM(Kosten) AS Kosten
FROM
Proj_KRech
GROUP BY ProjNrID
UNION
SELECT
ProjNrID,
SUM(Kosten) AS Kosten
FROM
Proj_KArt
GROUP BY ProjNrID) AS Kosten_Tab
GROUP BY ProjNrID

Besonders glücklich bin ich darüber jedoch nicht, gibt es eine Möglichkeit dies in einer Abfrage ohne SubSelect und UNION zu erledigen?
Vielen Dank für's Antworten!
</pre>

[Falk Prüfer]

Hallo Philipp,

eigentlich sollte das doch so gehen oder hab ich einen Denkfehler?<pre>
SELECT A.ProjNrID, SUM(A.Kosten) + SUM(B.Kosten) + SUM(C.Kosten) AS GesKosten
FROM Proj_KKst A,
Proj_KRech B,
Proj_KArt C
WHERE A.ProjNrID = B.ProjNrID
AND A.ProjNrID = C.ProjNrID
GROUP BY A.ProjNrID</pre>

Gruß Fal

[Philipp Krebs]

Hallo Falk,<br>
<p>
die Idee hatte ich auch als erstes - Allerdings klappt die Verknüpfung per JOIN nicht.<br>
Beim normalen JOIN werden nur Spalten angezeigt, bei denen alle drei Kostenarten vorhanden sind.<br>
Nutzt man jedoch ein LEFT JOIN o.ä., so werden bei denen Tabellen, die weniger Einträge als die anderen haben, die Einträge wiederholt. Nimmt man SUM heraus, um zu sehen was summiert wird, sieht das so aus:</p>
<pre>
ProjNrID Kosten Kosten GesKosten
----------- ----------------------------------------------------- ----------------------------------------------------- -----------------------------------------------------
8027 527.65999999999997 8786.0 NULL
8027 527.65999999999997 8900.0 NULL

(2 row(s) affected)
</pre>
<p>Obwohl die Arbeitszeit für 527 € nur einmal eingetragen wurde, zeigt der SQL Server sie doppelt, was ja auch eigentlich korrekt ist. Und ein SUM DISTINCT versagt erstens wiederum, wenn ein Datensatz tatsächlich mal mit den gleichen Kosten in der Datenbank steht, zweitens dauert die Abfrage sogar noch länger wie meine umständliche Variante!
</p&gt

[Falk Prüfer]

Hallo Philipp,

da brauchst du ja sogar einen FULL OUTER JOIN über drei Tabellen, da ja aus jeder Tabelle alle Einträge aufsummiert werden müssen! Eigentlich fehlt für die Abfrage noch eine Tabelle in der ALLE ProjNrID enthalten sind, um als Ausgangspunkt für einen OUTER JOIN zu dienen. Um den Effekt der "doppelten Zeilen" zu vermeiden darfst du dann den JOIN nicht auf die Tabellen machen, sondern auf entsprechende SubSelects (oder Views) die schon die "Zwischensummen" pro ProjNrID enthalten. Ob das ganze dann performanter als deine jetzige Lösung ist ... mmh ...? (Und schöner siehts auch nicht aus

Gruß Fal

[Philipp Krebs]

Hallo Falk,<br>
<p>
Letztlich bin ich mit deinem Tipp zum Ziel bekommen, meine Abfrage lautet jetzt:
</p>
<pre>
SELECT
P.ID AS ID,
CAST(COALESCE(sumKst.Kosten, 0) AS MONEY) AS Arbeitskosten,
CAST(COALESCE(sumKst.Aufwand, 0) AS MONEY) AS Arbeitszeit,
CAST(COALESCE(sumRech.Kosten, 0) AS MONEY) AS Rechnungskosten,
CAST(COALESCE(sumArt.Kosten, 0) AS MONEY) AS Materialkosten,
CAST(COALESCE(sumKst.Kosten, 0) + COALESCE(sumRech.Kosten, 0) + COALESCE(sumArt.Kosten, 0) AS MONEY) AS Gesamtkosten
FROM
Proj_Nr P
FULL OUTER JOIN
/* Umrechnung Minuten -> Manntage */
(SELECT ProjNrID, SUM(Kosten) AS Kosten, SUM(Zeit) / (60 / 8) AS Aufwand FROM Proj_KKst GROUP BY ProjNrID) AS sumKst ON sumKst.ProjNrID = P.ID
FULL OUTER JOIN
(SELECT ProjNrID, SUM(Kosten) AS Kosten FROM Proj_KRech GROUP BY ProjNrID) AS sumRech ON sumRech.ProjNrID = P.ID
FULL OUTER JOIN
(SELECT ProjNrID, SUM(Kosten) AS Kosten FROM Proj_KArt GROUP BY ProjNrID) AS sumArt ON sumArt.ProjNrID = P.ID
</pre>
<p>Als "Nebenprodukt" sozusagen gibt es auch noch die Einzelsumme der Kosten, die ich an anderer Stelle ebenfalls benötige.<br>
Von der Geschwindigkeit wurde das Query allerdings nicht besser, statt dem Merge/Union macht der server jetzt halt das Full Outer Join, aber damit kann ich Leben :-)<br>
Also vielen Dank für die Hilfe!</p&gt