Dataset we are using in this tutorial is Northwind Sample Superstore Dataset

Question: How many products are there in each Category

First we write the code without join:

SELECT CategoryID, COUNT(ProductID)
FROM productsGROUP BY CategoryID;

Now we combine Group By and Join together

SELECT suppliers.ContactName, COUNT(products.ProductID)
FROM products
JOIN suppliers
ON suppliers.SupplierID = products.SupplierID
GROUP BY suppliers.ContactName;

SELECT categories.CategoryName, COUNT(products.ProductID)
FROM products
JOIN categories
ON categories.CategoryID= products.CategoryID
GROUP BY categories.CategoryName;