Write a query that counts orders per customer

Join customers to orders and group by customer. Use a LEFT JOIN so customers without orders are still listed, and count a column of the orders table, not *.

SELECT c.id,
       c.name,
       COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY order_count DESC;

With a LEFT JOIN, a customer with no orders still produces one row, whose o.id is NULL. COUNT(o.id) ignores NULLs and returns 0, but COUNT(*) would count that row and wrongly return 1.

Every selected column that is not an aggregate must appear in GROUP BY. Some engines accept only c.id when it is the primary key, but listing both is portable.

If you only need customers who ordered, an INNER JOIN is enough. If you want customers with at least 5 orders, filter the groups with HAVING COUNT(o.id) >= 5.

Remember: count a column from the optional side of the join.