I realise that this was very much an introductory article, but the problem with doing it that way is that, while it's simple and clean, it isn't particularly efficient.
When mixing DISTINCT aggregate functions with other aggregate functions the execution plan will end up doing each DISTINCT aggregation separately from the other aggregations (probably using a temporary spool table) and then rejoin the results.
Where it's possible, an alternative that's usually more efficient is to partially aggregate by hand.
In this case:
SELECT
CustomerName,
COUNT(*) AS OrderCount,
SUM(LineCount) AS LineCount,
SUM(OrderAmount) AS TotalAmount
FROM (
SELECT
H.CustomerName,
COUNT(D.DetailID) as LineCount,
SUM(D.LineAmount) as OrderAmount
FROM
OrderHeader H
JOIN OrderDetail D ON H.OrderID = D.OrderID
GROUP BY
H.CustomerName, H.OrderId
) AS A
GROUP BY CustomerName
Article: Counting Parents and Children with Count Distinct
Posted - 01/10/2006 : 07:46:16
