Fan-out and double-counting

A valid query can still produce wrong numbers.

UnderstandStudy an examplePractise

Understand the concept

A one-to-many join multiplies rows. After joining orders to lines, COUNT(*) no longer counts orders. Joining two separate one-to-many children to the same parent can produce an even larger multiplication. Successful execution is not proof of a correct metric.

Preserve the metric’s grain. Aggregate line amounts per order before joining to customers. COUNT(DISTINCT OrderId) can correct an order count, but SUM(DISTINCT Amount) can wrongly merge different orders with equal amounts. DISTINCT is not a modelling cure.

WHEN TO USE IT

Use this reasoning to validate revenue, counts and averages after joins.

COMMON PITFALL

Hiding double-counting with SUM(DISTINCT Total).

01 / PRACTICE

One topic. Three levels of depth.

T-SQL / SQL Server 2019+

SELECT COUNT(*) AS JoinedRows, COUNT(DISTINCT o.OrderId) AS Orders FROM lab.SalesOrder AS o
JOIN lab.OrderItem AS i ON i.OrderId = o.OrderId;

18 joined lines, but 12 actual orders.

This page does not execute SQL. Practise in the downloadable SqlStudioLab using SSMS. Before rerunning temporary-table examples, remove only the relevant #table. Views, procedures and functions create persistent lab objects; never use a production connection.

02 / YOUR TURN

Try it yourself first.

Count paid orders correctly when joining their lines.

Show the solution and explanation

Worked solution

SELECT COUNT(DISTINCT o.OrderId) AS PaidOrders FROM lab.SalesOrder AS o JOIN lab.OrderItem AS i ON i.OrderId = o.OrderId WHERE o.Status = 'Paid';

Returns 9; COUNT(*) would count 15 paid lines.

RasimAtaberkŞahin

Understand the concept

One topic. Three levels of depth.

Try it yourself first.