finiteloop finiteloop - 1 year ago 86
SQL Question

SQL query many-to-many relationship count specific condition count 0 for those without

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;

Answer Source

Never use commas in the FROM clause. Always use explicit JOIN syntax. It is correct, modern, and more powerful.

You want a LEFT JOIN:

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;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download