CODEWITHSUNDEEP
sqlpostgresqlcountgroup-by
cryptic_star
cryptic_star

Reputation: 1873

SQL COUNT of COUNT

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!

Upvotes: 4

Views: 4204

Answers (1)

Jose Chama
Jose Chama

Reputation: 2978

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

Upvotes: 8

Related Questions