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
9. Grouping by the correct 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.first_name,
  e.last_name,
  COUNT(*) AS orders_count
FROM orders o
JOIN employees e
  ON o.employee_id = e.employee_id
GROUP BY e.first_name,
  e.last_name;

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.employee_id,
  e.first_name,
  e.last_name,
  COUNT(*) AS orders_count
FROM orders o
JOIN employees e
  ON o.employee_id = e.employee_id
GROUP BY e.employee_id,
  e.first_name,
  e.last_name;

We've added the employee_id 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 customer_id, company_name, and orders_count columns.

Stuck? Here's a hint!

Group by customer_id and company_name.