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
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