SQL COUNT of COUNT

Posted by cryptic-star on Stack Overflow See other posts from Stack Overflow or by cryptic-star
Published on 2010-03-11T05:13:42Z Indexed on 2010/03/11 5:18 UTC
Read the original article Hit count: 469

Filed under:
|
|

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!

© Stack Overflow or respective owner

Related posts about sql

Related posts about count