USE master
GO

WITH CTE(CUSTOMERID,BroughtByCustomerID) AS
(
  SELECT 1,NULL
    UNION ALL
  SELECT 2,1
    UNION ALL
  SELECT 3,1
    UNION ALL
  SELECT 4,2
    UNION ALL
  SELECT 5,4
    UNION ALL
  SELECT 6,1
    UNION ALL
  SELECT 7,NULL
    UNION ALL
  SELECT 8,3
    UNION ALL
  SELECT 9,4
    UNION ALL
  SELECT 10,2            
          
),
COUNTBROUGHT AS
(
  SELECT C.BroughtByCustomerID,COUNT(C.BroughtByCustomerID)CNT
  FROM CTE AS C
  GROUP BY C.BroughtByCustomerID
)
SELECT C.CUSTOMERID,ISNULL(BROUGHT.CNT,0)AS COUNTR
FROM CTE AS C
LEFT JOIN COUNTBROUGHT AS BROUGHT ON C.CUSTOMERID=BROUGHT.BroughtByCustomerID