How to get the Customer Detail + whether he has (an) order or not

Question

I have 2 tables. Customers and Orders.
enter image description here

My requirement is...

I would like to get the result like the following Customer Detail + HasOrders + Count(Orders)

I wrote

SELECT Customers.*
, CASE WHEN o.CustomerID IS NOT NULL THEN 1 ELSE 0 END HasOrders
FROM Customers c
LEFT JOIN Orders o
ON c.CustomerID = o.CustomersID

But it returns many rows. If the customer has 5 orders, it returns 5 rows for each Customer.

Could you please advise me? Thanks.

i'm sorry but i have no other way to communicate with you. can you answer my question in comments here [link](http://stackoverflow.com/questions/10244297/get-the-list-of-groups-for-the-given-userprincipal)? — donRumatta, Aug 09 '12 at 08:48

score 0 · Accepted Answer · answered Aug 03 '12 at 12:42

You need to do the counting in derived table.

SELECT c.*
     , case when o.CustomerID is not null
            then 1 
            else 0 
        end HasOrders
     , o.NumberOfOrders
FROM Customers c
LEFT JOIN
(
     SELECT CustomerID
          , count(*) NumberOfOrders
       FROM Orders
      GROUP BY CustomerID
) o
ON c.CustomerID = o.CustomersID

How to get the Customer Detail + whether he has (an) order or not

1 Answers1