I have a table like below,

If I group the table by Name to get the count,

Select Name, Count(*) as count from table group by Name;

I will get the following result,

I need to get the number of repetitions of each count. Means desired outcome below,

CodePudding user response：

Toss your existing sql into a subquery and group once again:

SELECT ncount, count(*) numberOfTimes
FROM (Select Name, Count(*) as ncount from table group by Name) dt
GROUP BY ncount

CodePudding user response：

Use COUNT(*) window function in your query:

SELECT DISTINCT COUNT(*) AS count,
       COUNT(*) OVER (PARTITION BY COUNT(*)) AS numberOfTimes
FROM tablename 
GROUP BY Name;

See the demo.