Having following tables:

Order table:


CustomerOrderLink table:

CustomerID  OrderID
1           3
1           4
2           2
2           4
3           3
4           4

Customer table:

CustomerID    Forename  Surname
1             A         Guy
2             Ted       Something
3             Gaby      Guy
4             Katie     Kitty

the task is to select all orders made by more than one person, but only when all the persons making order have same surname. Let's say that when customers have matching surname, they are a family. So to rephrase it: We want to have all the orders all families made together (we consider a family ordering when there are two or more customers with same surname for single order).

So the expected result for dummy data above would be:

OrderID Customers
3       2

I already have a part for selecting orders that are made by more than one customer:

 select OrderID, COUNT(distinct CustomerID) as CustomersCount
 from CustomerOrderLink group by OrderID having count(distinct CustomerID) > 1

but I struggle to implement checking, wheter they have matching surname:

select OrderID, COUNT(distinct CustomerID) as CustomersCount left join Customer on Customer.CustomerID = CustomerOrderLink.CustomerID 
     from CustomerOrderLink group by OrderID having count(distinct CustomerID) > 1 and ??? 

I'll be thankful for help.


Replace the ??? in your attempted query with

COUNT(DISTINCT Customer.Surname) = 1

So, the whole query, with a couple of improvements, would be

  CustomerCount = COUNT(distinct c.CustomerID),
  FamilyName    = MAX(c.Surname)
  dbo.CustomerOrderLink AS co
  INNER JOIN dbo.Customer AS c ON c.CustomerID = co.CustomerID 
  COUNT(DISTINCT c.CustomerID) > 1
  AND COUNT(DISTINCT c.Surname) = 1

An inner join would be better here since orders without associated customers would not match the COUNT(DISTINCT c.CustomerID) > 1 condition anyway. Talking of that condition, if the CustomerOrderLink table can never have more than one CustomerID/OrderID pair (and it should not), then you can replace it with COUNT(c.CustomerID) > 1 or even COUNT(*) > 1.

Note that if Customer.Surname is a nullable column and some customers do have a null there, the above may not return what you expect, because COUNT ignores nulls (and so it will return 1 if a group contains a couple of customers with the same surname and the rest without a surname).


Okay... Almost certainly not optimal, but my first thought was to use a common table expression. This could be easily rewritten to use a subquery:

;WITH      cte ( customerid, orderid, surname )
        AS ( SELECT   c.customerid
            ,       c.orderid
            ,       c2.surname
            FROM        customerorderlink AS c
            JOIN        customer AS c2
                    ON c2.customerid = c.customerid
    SELECT orderid
    ,     COUNT(*) AS Customers
    FROM      cte
    GROUP BY orderid
    ,     surname
    HAVING COUNT(*) > 1;
SELECT OrderId , Surname , COUNT(1) Count FROM Order 
        INNER JOIN CustomerOrderLink 
    ON Order.OrderId = CustomerOrderLink.CustomerId
        INNER JOIN Customer 
    ON CustomerOrderLink.CustomerId = Customer.CustomerId 
                   GROUP BY OrderId 
                   Surname HAVING COUNT(1) > 1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.