COUNT in COUNT OVER PARTITION SQL Server

Question

SELECT MobilePhone, COUNT(MobilePhone) OVER(PARTITION BY MobilePhone) AS CountMobilePhone
FROM ES_TABLE 
WHERE applic IN ( SELECT appl 
                FROM Campaign JOIN Client ON Campaign.ClientID = Client.ClientID 
                WHERE Client.ClientCode = 'OPIS')

thats my query

the output is

MobilePhone  | CountMobilePhone
121928       |    1
912819       |    1
129819       |    3
198219       |    5
918298       |    5

I want to group by CountMobilePhone for counting. to be like this

Count   | CountMobilePhone
2       |    1
1       |    3
2       |    5

is that possible? I've tried several times but getting error

Answer 1

Just do another aggregation on the counts themselves:

SELECT CountMobilePhone, COUNT(*) AS cnt
FROM
(
    SELECT COUNT(MobilePhone) OVER(PARTITION BY MobilePhone) AS CountMobilePhone
    FROM ES_TABLE 
    WHERE applic IN (SELECT appl FROM Campaign INNER JOIN Client
                         ON Campaign.ClientID = Client.ClientID 
                     WHERE Client.ClientCode = 'OPIS')
) t
GROUP BY CountMobilePhone
ORDER BY CountMobilePhone;