Vervielfachte Berichtssummen durch Joins verhindern

Ein Bericht verbindet Aufträge, Positionen und Zahlungen, gruppiert nach Auftrag und liefert genau eine Zeile pro Auftrag. Die Form stimmt, die Summen jedoch nicht. GROUP BY kann zusätzliche Kombinationen verbergen, ohne ihren Einfluss auf SUM aufzuheben. Entscheidend ist, welche fachliche Einheit jede Eingabezeile darstellt.

Kombinationen vor der Summe verstehen

Ein Auftrag mit zwei Positionen und drei Zahlungen erzeugt sechs Kombinationen, wenn beide Kindmengen unabhängig über OrderId verbunden werden. Jede Position erscheint dreimal, jede Zahlung zweimal. SQL Server folgt dabei exakt den Bedingungen und dupliziert keine gespeicherten Datensätze versehentlich.

Im Beispiel haben beide Positionen absichtlich denselben Betrag. Dadurch wird auch der Fehler von SUM(DISTINCT Amount) sichtbar.

DECLARE @Orders TABLE (OrderId int PRIMARY KEY);
DECLARE @Lines TABLE
(LineId int PRIMARY KEY, OrderId int, Amount decimal(12,2));
DECLARE @Payments TABLE
(PaymentId int PRIMARY KEY, OrderId int, Amount decimal(12,2));

INSERT @Orders VALUES (1), (2);
INSERT @Lines VALUES (11, 1, 10), (12, 1, 10);
INSERT @Payments VALUES (21, 1, 5), (22, 1, 7), (23, 1, 8);

SELECT o.OrderId,
       SUM(l.Amount) AS WrongLineTotal,
       SUM(p.Amount) AS WrongPaymentTotal
FROM @Orders AS o
LEFT JOIN @Lines AS l ON l.OrderId = o.OrderId
LEFT JOIN @Payments AS p ON p.OrderId = o.OrderId
GROUP BY o.OrderId;

;WITH L AS
(
    SELECT OrderId, SUM(Amount) AS LineTotal
    FROM @Lines GROUP BY OrderId
),
P AS
(
    SELECT OrderId, SUM(Amount) AS PaymentTotal
    FROM @Payments GROUP BY OrderId
)
SELECT o.OrderId,
       COALESCE(l.LineTotal, 0) AS LineTotal,
       COALESCE(p.PaymentTotal, 0) AS PaymentTotal
FROM @Orders AS o
LEFT JOIN L AS l ON l.OrderId = o.OrderId
LEFT JOIN P AS p ON p.OrderId = o.OrderId
ORDER BY o.OrderId;

Für Auftrag 1 liefert die erste Abfrage 60 als Positionssumme und 40 als Zahlungssumme. Richtig sind 20 und 20. Die zweite Abfrage aggregiert jede Kindmenge zuerst auf eine Zeile pro Auftrag. Auftrag 2 bleibt erhalten und erhält hier null im Sinne des Zahlenwerts 0, weil fehlende Positionen und Zahlungen fachlich so interpretiert werden.

SUM(DISTINCT l.Amount) ergäbe 10 statt 20. Es entfernt gleiche Werte, nicht zusätzliche Vorkommen derselben Position. Zwei echte Positionen können denselben Preis haben. Ein abschließendes SELECT DISTINCT repariert ebenfalls keine bereits vervielfachte Summe.

Entfernen Sie bei der Diagnose vorübergehend die Aggregation und zeigen Sie Auftragsschlüssel sowie Primärschlüssel jeder Kindtabelle. So werden Kombinationen sichtbar. Vergleichen Sie Zeilenzahlen vor und nach jedem Join und suchen Sie den ersten Wechsel der gewünschten Ebene.

Auf der benötigten Ebene aggregieren

Die korrigierten CTEs liefern durch GROUP BY höchstens eine Zeile pro OrderId. Nicht ihre Namen und keine automatische Materialisierung machen den Join sicher, sondern diese Gruppierung. Abgeleitete Tabellen oder passende aggregierende APPLY-Ausdrücke können dieselbe Bedeutung ausdrücken.

OrderId ist nicht immer die richtige vollständige Ebene. Ein Bericht nach Auftrag und Währung darf unterschiedliche Währungen nicht in einer Summe verlieren. Ein Produktbericht benötigt Informationen, die eine reine Auftragssumme bereits entfernt hat. Definieren Sie zuerst den Schlüssel der Ausgabe.

Wenden Sie Filter auf die passende Kennzahl an. Soll der gesamte Auftragswert mit nur bestätigten Zahlungen verglichen werden, filtern Sie die Zahlungseingabe vor ihrer Aggregation. Eine Bedingung auf der rechten Seite im abschließenden WHERE kann Aufträge ohne passende Zahlung entfernen und den LEFT JOIN faktisch in einen INNER JOIN verwandeln.

Wird eine Kindtabelle nur für eine Existenzbedingung benötigt, verwenden Sie EXISTS. Die Forderung nach mindestens einer genehmigten Zahlung bedeutet nicht, dass jede Zahlung die Positionsmenge vervielfachen soll. Eine klare Formulierung schützt Bedeutung und oft auch Ressourcenverbrauch.

Ungleichmäßige Testfälle verwenden

Testen Sie Aufträge ohne Kinder, mit je einem Kind, mit mehreren Positionen und einer Zahlung sowie mit mehreren Kindern auf beiden Seiten. Nehmen Sie gleiche Beträge, Teilzahlungen, Erstattungen und erlaubte NULL-Beträge auf. Reine Eins-zu-eins-Beispiele verbergen die Ursache.

COALESCE auf 0 ist eine fachliche Berichtsentscheidung. Steht eine fehlende Summe für unbekannte Daten oder unvollständigen Import, kann 0 falsch informieren. Auch COUNT(*) nach LEFT JOIN zählt die erhaltene Elternzeile ohne Kind. Für tatsächliche Kinder zählen Sie einen nichtleeren Kindschlüssel.

Eindeutige Constraints auf Dimensionen schützen gegen eine weitere Vervielfachung: doppelte Zuordnungen in einer vermeintlich eindeutigen Nachschlagetabelle. Prüfen Sie den wirklichen Schlüssel einschließlich Mandant und gegebenenfalls Gültigkeitszeit. Ein Name oder eine Anwendungskonvention beweist keine Eindeutigkeit.

Erst nach der fachlichen Prüfung untersuchen Sie tatsächlichen Plan und Indizes für Gruppierung und Joins. Voraggregation kann Zwischendaten reduzieren, ersetzt aber keine korrekte Kennzahldefinition. Bewahren Sie einen kleinen Test mit ungleichen Kindanzahlen auf, damit eine spätere Vereinfachung die falschen Summen nicht zurückbringt.

Technische Referenzen: Microsoft Learn: JOIN semantics · Microsoft Learn: SUM.