Avoiding misleading aggregate results

This lesson uses the unmodified DDI_SQL_Training dataset v1.0.0 on SQL Server 2019 or later. Recognize how a one-to-many join can inflate an order count.

The example uses JOIN, which Chapter 6 teaches in full. For now, read JOIN dbo.OrderItems AS i ON i.OrderID = o.OrderID as: pair each order with every order-item row that has the same OrderID.

Run the example

SELECT COUNT(*) AS JoinedRows,
       COUNT(DISTINCT o.OrderID) AS DistinctOrders
FROM dbo.Orders AS o
JOIN dbo.OrderItems AS i ON i.OrderID = o.OrderID
WHERE o.OrderID BETWEEN 1 AND 3;

Read it step by step

1. Each selected order has two OrderItems rows in dataset v1.0.0.
2. The join therefore produces six rows for three orders.
3. COUNT(DISTINCT o.OrderID) counts order identities rather than joined lines.

Check the result

JoinedRows and DistinctOrders answer different questions; neither is inherently wrong.

Practice

Repeat the comparison for OrderID values 10 through 12.

SELECT COUNT(*) AS JoinedRows,
       COUNT(DISTINCT o.OrderID) AS DistinctOrders
FROM dbo.Orders AS o
JOIN dbo.OrderItems AS i ON i.OrderID = o.OrderID
WHERE o.OrderID BETWEEN 10 AND 12;