Anson Anson - 4 months ago 8
SQL Question

How to count how many unique records with multiple records in one table

I have customer table as the following:

CustomerID, ReferenceID
1 ,101
2 ,101
3 ,101
4 ,102
5 ,102
6 ,103


I want to count how many ReferenceID has multiple customerID and I write the following query:

SELECT CustomerID, ReferenceID, Count(1)
FROM Customer
group by CustomerID, ReferenceID
having Count(1) >1


I should get number 2 because of 2 ReferenceID have multiple customerID , but I do not get any number

Answer
DECLARE @ReferenceIdCount INT

SELECT @ReferenceIdCount = COUNT(*)
FROM
    (
       SELECT
          ReferenceId
          ,COUNT(DISTINCT CustomerId) as ReferenceCount
       FROM
          @Table
       GROUP BY
          ReferenceId
       HAVING
          COUNT(DISTINCT CustomerId) > 1
    ) t

First you have to find the ReferenceId's that have multiple CustomerIds then next you have to count them. So you can do this many ways but the nested select is an easy way to show you.