Anson Anson - 1 year ago 69
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 Source
DECLARE @ReferenceIdCount INT

SELECT @ReferenceIdCount = COUNT(*)
          ,COUNT(DISTINCT CustomerId) as ReferenceCount
       GROUP BY
          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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download