Empêcher les jointures de multiplier les totaux SQL

Un rapport joint commandes, lignes et paiements, puis groupe par commande. Il retourne une ligne par commande, mais les totaux sont faux. GROUP BY peut masquer les combinaisons supplémentaires sans annuler leur effet sur SUM. La question essentielle est la granularité : que représente réellement une ligne de chaque entrée ?

Compter les combinaisons avant de sommer

Une commande avec deux lignes et trois paiements produit six combinaisons si les deux ensembles enfants sont joints indépendamment par OrderId. Chaque ligne apparaît trois fois, chaque paiement deux fois. La base applique exactement les conditions demandées.

Les deux lignes ont volontairement le même montant dans cet exemple. Cela montre également pourquoi SUM(DISTINCT Amount) n'est pas une réparation.

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;

Pour la commande 1, la première requête retourne 60 pour les lignes et 40 pour les paiements. Les vrais totaux sont 20 et 20. La seconde agrège chaque ensemble à une ligne par commande avant la jointure. La commande 2 reste présente avec des zéros, selon l'interprétation choisie ici pour l'absence de détails.

SUM(DISTINCT l.Amount) retournerait 10 au lieu de 20. Il supprime les valeurs égales, pas les occurrences supplémentaires d'une même ligne identifiée. Deux lignes légitimes peuvent partager un prix. Un SELECT DISTINCT final ne répare pas non plus une somme déjà multipliée.

Pour diagnostiquer un rapport complexe, retirez temporairement l'agrégation et affichez clé parent et clés primaires des enfants. Les combinaisons deviennent visibles. Comptez avant et après chaque jointure pour trouver le premier changement de granularité.

Agréger au niveau réellement demandé

Les CTE corrigées garantissent au plus une ligne par OrderId grâce à GROUP BY. Leur nom ne les matérialise pas automatiquement ; c'est la propriété logique du regroupement qui protège le résultat. Des tables dérivées ou des expressions APPLY avec agrégation peuvent exprimer la même chose.

OrderId seul n'est pas toujours suffisant. Un rapport par commande et devise ne peut additionner silencieusement plusieurs monnaies. Un rapport par produit exige des détails déjà perdus dans un total par commande. Écrivez la clé de sortie attendue avant de choisir les regroupements.

Appliquez les filtres à la mesure concernée. Pour comparer toute la valeur commandée aux seuls paiements réglés, filtrez les paiements avant leur agrégation. Une condition finale dans WHERE sur le côté droit peut supprimer les commandes sans paiement et transformer effectivement le LEFT JOIN en jointure interne.

Si un enfant sert seulement à exiger une présence, utilisez EXISTS. Demander au moins un paiement approuvé ne signifie pas que chaque paiement doit multiplier les lignes. Exprimer directement cette intention protège le sens et souvent le volume traité.

Vérifier avec des cas asymétriques

Testez une commande sans enfants, avec un enfant de chaque côté, plusieurs lignes et un paiement, puis plusieurs enfants des deux côtés. Incluez montants égaux, paiements partiels, remboursements et états NULL autorisés. Des exemples uniquement un-à-un dissimulent le défaut.

COALESCE vers zéro est une décision de présentation métier. Une agrégation absente peut signifier données inconnues ou import incomplet, auquel cas zéro trompe le lecteur. COUNT(*) après LEFT JOIN compte aussi la ligne parent préservée sans enfant. Comptez une clé enfant non NULL si vous voulez le nombre d'enfants.

Des contraintes uniques sur les clés de dimensions évitent une autre multiplication : plusieurs correspondances dans une table supposée unique. Vérifiez le vrai identifiant, incluant tenant et période de validité si nécessaire. Une convention applicative ne suffit pas.

Après validation du résultat, examinez plan réel et index des clés de jointure et de regroupement. La préagrégation peut réduire les intermédiaires, mais ne remplace pas une mesure bien définie. Conservez un petit jeu de régression avec des nombres d'enfants différents pour empêcher une future simplification de réintroduire les totaux gonflés.

Références techniques: Microsoft Learn: JOIN semantics · Microsoft Learn: SUM.