I have the following Models set up for the DB:
SQL DB Models
I am wondering how to query the number of orders for each customer whose status is 'on reorder'and think that I have it pretty close but not quite what I want to accomplish because it is counting just those customers who have 'on reorder' on their status and not all customers (which otherwise should count to 0 where 'on reorder' is not applicable).
I am pretty sure I need to achieve this with a subquery or a join. Any help would be appreciated. Here is what I have thus far:
SELECT customerName, orderNumber, COUNT(*) AS reorder_count
FROM Orders, Customers
WHERE Orders.customerNumber = Customers.customerNumber
AND status = 'on reorder'
GROUP BY orderNumber, customerName;
Never use commas in the
FROM clause. Always use explicit
JOIN syntax. It is correct, modern, and more powerful.
You want a
SELECT c.customerName, COUNT(o.orderNumber) AS reorder_count FROM Customers c LEFT JOIN Orders o ON o.customerNumber = c.customerNumber AND o.status = 'on reorder' GROUP BY c.customerName;