Deals Of The Week - 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
12. Counting occurrences of business objects
Summary

Instruction

Perfect! When writing reports in SQL, you have to remember that some objects may not exist. In this exercise we discuss a very common error in using COUNT() with LEFT JOIN. The next report we'd like to create should count orders for three different customer IDs: 'ALFKI', 'FISSA', and 'PARIS':

SELECT
  c.customer_id,
  COUNT(o.order_id) AS order_count
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.customer_id
WHERE c.customer_id IN ('ALFKI', 'FISSA', 'PARIS')
GROUP BY c.customer_id;

Note the following:

  1. We used LEFT JOIN to make sure we'll see all three customer IDs in the report. If we used a simple JOIN and any of the customers placed no orders, they would not be shown in the report.
  2. We used COUNT(o.order_id) instead of COUNT(*). This ensures that we only count rows with non-NULL order_id column values. This is important if a customer hasn't ordered anything – in that case, COUNT(*) would return 1 instead of 0 because there would be one row with the given customer_id and a NULL value in the order_id column.

Exercise

Find the total number of products provided by each supplier. Show the company_name and products_count (the number of products supplied) columns. Include suppliers that haven't provided any products.

Stuck? Here's a hint!

Use a RIGHT JOIN on the products and suppliers tables. Remember to include the supplier_id in the GROUP BY clause.