Count CustomersOrders

Question

Dear Team
  if there are three tables customers, Order  and OrderDetails
  how to count the number of orders for each certain customer

 Thank you very much

Answer 1

Assuming you have the CustomerID in the Orders table as a foreign key back to the Customers table, you can do a group by on the Orders.CustomerID and do a count. Using the Northwind database schema:

 

select CustomerID, count(OrderID)
from Orders
group by CustomerID

This will only give you results for the customers that have orders. If you need the customers without orders as well, you will need to include the Customers table. For example with a left outer join:

select C.CustomerID, count(O.OrderID)
from Customers C left outer join Orders O on C.CustomerID=O.CustomerID
group by C.CustomerID

Best regards

Michael

<[e-mail]> wrote in message news:[e-mail]...

Dear Team
  if there are three tables customers, Order  and OrderDetails
  how to count the number of orders for each certain customer

 Thank you very much

Answer 2

Mr NNTP
 The Query looks good thank you very much
 i will take some time to test it