Summer 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 order. The following query will give us both the total count and the number of unique items:

SELECT 
  order_id, 
  COUNT(product_id) AS products_count, 
  COUNT(DISTINCT product_id) AS unique_products_count
FROM order_items
GROUP BY order_id;

We used COUNT(product_id) and COUNT(DISTINCT product_id). The difference is that COUNT(product_id) counts all products in an order and COUNT(DISTINCT product_id) only counts unique products. This means that COUNT(DISTINCT product_id) will yield a lower value than COUNT(product_id) when a product_id appears more than once in a given order. (The same product_id can appear several times in an order – even though we've got a quantity column – when a customer adds more products into his or her order at a later time).

Exercise

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

Stuck? Here's a hint!

Join the orders and customers tables. Use COUNT(DISTINCT c.customer_id).