Freight That Tripled on the Way Through a JOIN

The join changed the grain of the data. Orders is one row per order; OrderLines is one row per product on an order. Joining them produces one row per line, with the order's Freight repeated on each of them. SUM(o.Freight) then adds each order's freight once per line: an order with four products contributes its freight four times.

That also explains the varying ratio. A customer whose orders average five lines inflates 5×, a customer with single-line orders inflates 1×, and the portfolio lands at 3.3×. A constant multiple would have pointed at a duplicated join or a cross join; a varying one points at fan-out.

Corrected query - aggregate at the order grain only:

SELECT o.CustomerID,
       COUNT(*)       AS OrderCount,
       SUM(o.Freight) AS FreightTotal
FROM Orders o
GROUP BY o.CustomerID;

If line revenue is wanted in the same report, pre-aggregate the lines first, then join one row per order:

WITH L AS (
  SELECT OrderID,
         SUM(Qty * UnitPrice) AS LineTotal,
         COUNT(*)             AS LineCount
  FROM OrderLines
  GROUP BY OrderID
)
SELECT o.CustomerID,
       COUNT(*)                     AS OrderCount,
       SUM(o.Freight)               AS FreightTotal,
       SUM(COALESCE(L.LineTotal,0)) AS RevenueTotal
FROM Orders o
LEFT JOIN L ON L.OrderID = o.OrderID
GROUP BY o.CustomerID;

SUM(DISTINCT o.Freight) is not a fix - two genuine orders that happen to charge the same freight would be collapsed into one, understating the total.

The filter in the WHERE clause silently turned the outer join into an inner join. A LEFT JOIN produces a null-extended row for each customer with no matching order. WHERE o.OrderDate >= '2024-01-01' tests a column that is NULL on exactly those rows; NULL fails the comparison, so they are discarded. The same clause also removes every real customer whose orders are all older than the fiscal year - which is why 768 customers disappear, not just the never-ordered ones.

Corrected:

SELECT c.CustomerID,
       COUNT(o.OrderID) AS OrdersInPeriod,
       MAX(o.OrderDate) AS LastOrderDate
FROM Customers c
LEFT JOIN Orders o
       ON o.CustomerID = c.CustomerID
      AND o.OrderDate >= '2024-01-01'
GROUP BY c.CustomerID;

Two details decide whether the numbers are right:

• Predicate placement. The date filter belongs in ON. It restricts which orders match without removing the customer; the outer row survives with NULLs.
• COUNT(o.OrderID), never COUNT(*). On a customer with no orders the LEFT JOIN yields one row of NULLs, and COUNT(*) returns 1 - reporting every dormant customer as having placed an order. COUNT(column) skips NULLs and returns 0.

One further design point: if the report must show both "orders this year" and "last order ever", compute them in separate subqueries and join on CustomerID. Mixing a period filter and an all-time MAX in one aggregate gives a MAX over the filtered set, which is not the last order.

Test the grain directly in the query's own result set:

• SELECT COUNT(*), COUNT(DISTINCT o.OrderID) FROM Orders o JOIN OrderLines l ON o.OrderID = l.OrderID - if the counts differ, the join has fanned out. At order grain they must match (9,400 here).
• SELECT MAX(cnt) FROM (SELECT OrderID, COUNT(*) cnt FROM OrderLines GROUP BY OrderID) t gives the worst-case inflation factor; it should equal the largest ratio seen in the customer report.
• COUNT(*) versus COUNT(DISTINCT l.ProductID) reveals whether products are being double-counted on the line side.
• Compare the grouped row count with SELECT COUNT(DISTINCT CustomerID) FROM Orders; a shortfall means rows are being lost, not just duplicated.
• Sanity-check a measure the join cannot affect - read total freight straight from Orders and then through the join. Any difference is fan-out by definition.

The general rule: an aggregate is valid only when the grain of the FROM clause matches the grain of the measure. Freight lives at order grain, revenue at line grain. A query that needs both must aggregate each table at its own grain first - no single flat join can preserve both, and the error is invisible in the output because every figure still looks like money.