My JOIN doubled the totals: the SQL Server grain mistake

You joined Orders to OrderLines to get product names and the revenue doubled. Nothing is wrong with SUM. The grain changed and nobody told SUM.

What happened

One order of £90 with three lines becomes three rows after the join. SUM(o.total_amount) adds £90 three times.

-- Inflated
SELECT SUM(o.total_amount) AS revenue
FROM Orders o
JOIN OrderLines ol ON ol.order_id = o.order_id;

Aggregate the lines first

SELECT o.order_id, o.total_amount, x.lines, x.units
FROM Orders o
JOIN (
  SELECT order_id, COUNT(*) AS lines, SUM(quantity) AS units
  FROM OrderLines
  GROUP BY order_id
) x ON x.order_id = o.order_id;

Now one row per order. Totals stay honest. You can still SUM(o.total_amount) outside.