How to count the number of groups returned by a gr

问题:

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

This returns a number of matches for each foreign id. However what im looking for is to summarise the results.

So the result would be:

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

回答1:

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

You need to first know how many times each FK appears:

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

Once you have this, you have to group them to get the amount of times each item appears the same way as above. This will result in:

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

See it in action here

回答2:

Count the number of groups returned by a group by:

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

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