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
9. Grouping by the right columns
Understanding the difference between various count metrics
Summary

Instruction

Perfect! Now that we know how to show metrics for multiple business objects in a single report, we would like to show order counts for each employee at Northwind. You might be tempted to write the following query:

SELECT
  E.FirstName,
  E.LastName,
  COUNT(*) AS OrdersCount
FROM Orders O
JOIN Employees E
  ON O.EmployeeID = E.EmployeeID
GROUP BY E.FirstName,
  E.LastName;

The query seems fine at first sight, but it can actually produce incorrect results. Employees' first and last names may not be unique – imagine two employees that share the same name. In such a case, their orders will be grouped together, which is incorrect. Luckily, we can easily fix the query:

SELECT
  E.EmployeeID,
  E.FirstName,
  E.LastName,
  COUNT(*) AS OrdersCount
FROM Orders O
JOIN Employees E
  ON O.EmployeeID = E.EmployeeID
GROUP BY E.EmployeeID,
  E.FirstName,
  E.LastName;

We've added the EmployeeID column to the GROUP BY and SELECT clauses. Because employee IDs are unique, the query will now produce correct results.

Remember, you should always GROUP BY unique columns that differentiate between various business objects.

Exercise

Count the number of orders placed by each customer. Show the CustomerID, CompanyName, and OrdersCount columns.

Stuck? Here's a hint!

Group by CustomerID and CompanyName.