14.2 One-to-Many: Customers and Shipments (What One Row Means)

Why. Before you join or total anything, you must know what one row stands for. Mistakes about this are the most common reason a report shows the wrong number.

What. The grain of a table is what one row means. In customers, one row is one customer. In shipments, one row is one shipment. One customer can have many shipments, so this is a one-to-many relationship. A customer can also have no shipments at all, like Marina Spices.

How. Count the shipments for each customer id inside the shipments table, without joining anything yet.

SELECT customer_id,
       COUNT(*) AS shipments
FROM shipments
GROUP BY customer_id
ORDER BY customer_id;