Rainbow Deals - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Providing detailed information and counting objects
Calculating metrics for multiple business objects
Understanding the difference between various count metrics
13. Counting distinct objects
Summary

Instruction

Good job! Suppose we want to find the number of items in each order, but we know that the same product might appear multiple times in one order. In that case, we want the number of unique products in each of order. The following query will give us both the total count and the number of unique items:

SELECT 
  OrderID, 
  COUNT(ProductID) AS ProductsCount, 
  COUNT(DISTINCT ProductID) AS UniqueProductsCount
FROM OrderItems
GROUP BY OrderID;

We used COUNT(ProductID) and COUNT(DISTINCT ProductID). The difference is that COUNT(ProductID) counts all products in an order and COUNT(DISTINCT ProductID) only counts unique products. This means that COUNT(DISTINCT ProductID) will yield a lower value than COUNT(ProductID) when a ProductID appears more than once in a given order (the same ProductID can appear few times in an order – even though we've got a Quantity column – when a customer puts more products into his or her order after some time).

Exercise

Show the number of unique companies (as NumberOfCompanies) that had orders shipped to Spain.

Stuck? Here's a hint!

Join the Orders and Customers tables. Use COUNT(DISTINCT C.CustomerID).