Write a query to show country wise number of customers and number of orders: Run the query: SELECT count(a.customerid), count(b.orderid),a.country from Customers a inner join Orders b on a.customerid = b.customerid group by a.country
Observe the results. You will find number of orders for each country to be correct. But number of customers in each country is not correct. For example, query returns 13 as counts for both customers and orders in the country Argentina. This is because one customer can have multiple orders and each with order the customer count is also being increased.
To avoid this problem, you can use DISTINCT as below:
SELECT count(DISTINCT a.customerid), count(b.orderid),a.country from Customers a inner join Orders b on a.customerid = b.customerid group by a.country
Observer the results. The above query counts each customer only once.
If customers from a country has no orders yet, above query will not show that country. In such cases if you want to display number of customers from that country and a null value for number of orders, you should replace INNER JOIN with LEFT JOIN. However W3SCHOOLS does not support LEFT JOINS yet.