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

i have customer table following:

customerid, referenceid     1   ,101     2   ,101     3   ,101     4   ,102     5   ,102     6   ,103 

i want count how many referenceid has multiple customerid , write following query:

select customerid,  referenceid, count(1)   customer group customerid,    referenceid     having count(1) >1 

i should number 2 because of 2 referenceid have multiple customerid , not number

declare @referenceidcount int  select @referenceidcount = count(*)     (        select           referenceid           ,count(distinct customerid) referencecount                  @table        group           referenceid        having           count(distinct customerid) > 1     ) t 

first have find referenceid's have multiple customerids next have count them. can many ways nested select easy way show you.