SELECT cid, COUNT(DISTINCT ccid)
 FROM dbo.MyTable
 GROUP BY cid

CREATE TABLE #test
  (
     CID    INT,
     CCID   CHAR(10),
     CCOUNT INT
  )

INSERT INTO #test
VALUES      (2,'a',1),
            (2,'b',1)
-- Edited this part
SELECT *,

(SELECT SUM(ccount) FROM(SELECT  CCOUNT
        FROM   #test GROUP BY CID,CCOUNT)abc)CountTotal
FROM   #test