SQL COUNT of COUNT

Question

I have some data I am querying. The table is composed of two columns - a unique ID, and a value. I would like to count the number of times each unique value appears (which can easily be done with a COUNT and GROUP BY), but I then want to be able to count that. So, I would like to see how many items appear twice, three times, etc.

So for the following data (ID, val)...

1, 2
2, 2
3, 1
4, 2
5, 1
6, 7
7, 1

The intermediate step would be (val, count)...

1, 3
2, 3
7, 1

And I would like to have (count_from_above, new_count)...

3, 2 -- since three appears twice in the previous table
1, 1 -- since one appears once in the previous table

Is there any query which can do that? If it helps, I'm working with Postgres. Thanks!

Answer 1

Try something like this:

select
   times,
   count(1)
from ( select 
          id,
          count(distinct value) as times
       from table
       group by id ) a
group by times