I have customer table as the following:
SELECT CustomerID, ReferenceID, Count(1)
group by CustomerID, ReferenceID
having Count(1) >1
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.