Evitar que los joins multipliquen totales en SQL Server

Un informe une pedidos, líneas y pagos, agrupa por pedido y devuelve una fila por pedido. La forma parece correcta, pero los totales están inflados. GROUP BY puede esconder combinaciones adicionales sin deshacer su efecto sobre SUM. La pregunta decisiva es qué representa una fila de cada entrada.

Contar combinaciones antes de sumar

Un pedido con dos líneas y tres pagos genera seis combinaciones cuando ambas colecciones se unen independientemente mediante OrderId. Cada línea aparece tres veces y cada pago dos. SQL Server sigue exactamente las condiciones solicitadas.

Las dos líneas tienen deliberadamente el mismo importe. Así el ejemplo también demuestra por qué SUM(DISTINCT Amount) no arregla el problema.

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;

Para el pedido 1, la primera consulta devuelve 60 como total de líneas y 40 como pagos. Los valores reales son 20 y 20. La segunda agrega cada colección a una fila por pedido antes de unirla. El pedido 2 permanece con ceros porque aquí se interpreta la ausencia de detalles como cero.

SUM(DISTINCT l.Amount) devolvería 10, no 20. Elimina valores iguales, no apariciones adicionales de una misma línea identificada. Dos líneas legítimas pueden costar lo mismo. SELECT DISTINCT al final tampoco repara una suma que ya contó filas multiplicadas.

Para investigar una consulta grande, retira temporalmente la agregación y muestra la clave padre y las claves primarias hijas. Las combinaciones se vuelven visibles. Cuenta antes y después de cada join y localiza el primer cambio de granularidad.

Agregar al nivel necesario

Las CTE corregidas garantizan como máximo una fila por OrderId mediante GROUP BY. Sus nombres no implican materialización automática; la agrupación lógica hace segura la unión. Tablas derivadas o expresiones APPLY agregadas pueden expresar la misma idea.

OrderId no siempre define toda la granularidad. Un informe por pedido y moneda no puede mezclar divisas en un importe. Un informe por producto necesita detalle perdido al agregar solo por pedido. Escribe primero la clave esperada de salida.

Aplica filtros a la medida apropiada. Si necesitas todo el valor pedido pero solo pagos liquidados, filtra los pagos antes de agregarlos. Una condición final en WHERE sobre la tabla derecha puede eliminar pedidos sin pagos y convertir efectivamente LEFT JOIN en INNER JOIN.

Cuando una tabla hija solo exige existencia, usa EXISTS. Pedir al menos un pago aprobado no significa que cada pago deba multiplicar las líneas. Expresar esa intención directamente protege el significado y puede reducir trabajo.

Probar casos asimétricos

Incluye pedidos sin hijos, con uno a cada lado, varias líneas y un pago, y varios hijos en ambos lados. Prueba importes iguales, pagos parciales, devoluciones y valores NULL permitidos. Los ejemplos exclusivamente uno a uno esconden el defecto.

COALESCE a cero es una decisión de negocio. Si una agregación ausente representa información desconocida o importación incompleta, cero puede engañar. COUNT(*) tras LEFT JOIN también cuenta la fila padre preservada aunque no haya hijo. Cuenta una clave hija no NULL cuando quieras contar hijos reales.

Las restricciones únicas en dimensiones evitan otra causa de multiplicación: varias correspondencias en una tabla supuestamente única. Verifica la clave real, incluidos tenant y vigencia cuando corresponda. Una convención de nombres o de aplicación no demuestra unicidad.

Una vez correcta la medida, examina plan real e índices de agrupación y unión. Preagregar puede reducir intermediarios, pero el rendimiento viene después de una definición válida del cálculo. Conserva un conjunto pequeño de regresión con cantidades desiguales de hijos. Así una simplificación futura no volverá a producir sumas infladas que parecen razonables.

Referencias técnicas: Microsoft Learn: JOIN semantics · Microsoft Learn: SUM.