How to count the number of groups returned by a group?

Question

select count(*) as count from table group by foreign_id order by count 

This returns the number of matches for each foreign identifier. However, what they are looking for is a generalization of the results.

Thus, the result will be:

 10 results grouping 1 elements 5 results grouping 2 elements 7 results grouping 7 elements 

Answer 1

Ok, got it. The title of the question illuminates it better than the question itself :)

You need to first find out how many times each FK appears:

 select count(*) as GroupAmount from t1 group by foreign_id 

After that, you should group them to get the number of times each item is displayed as above. This will lead to:

 select GroupAmount, count(*) GroupAmountTimes from ( select count(foreign_id) as GroupAmount from t1 group by foreign_id ) as SubQuery group by GroupAmount 

See in action here

Answer 2

Count the number of groups returned by the group:

 select foreign_id as GroupAmount, count(foreign_id) as GroupAmountTimes from t1 group by foreign_id 

http://sqlfiddle.com/#!2/35661/42