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
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: N'ALFKI', N'FISSA', and N'PARIS':

SELECT
  C.CustomerID,
  COUNT(O.OrderID) AS OrderCount
FROM Customers C
LEFT JOIN Orders O
  ON O.CustomerID = C.CustomerID
WHERE C.CustomerID IN (N'ALFKI', N'FISSA', N'PARIS')
GROUP BY C.CustomerID;

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.OrderID) instead of COUNT(*). This ensures that we only count rows with non-NULL OrderID 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 CustomerID and a NULL value in the OrderID column.

Exercise

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

Stuck? Here's a hint!

Use a LEFT JOIN on the Products and Suppliers tables. Remember to include the SupplierID in the GROUP BY clause.