Menu
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
953
SQL – count() with Group By clause The count() function is an aggregate function use to find the count of the rows that satisfy the fixed conditions. The count() function with the GROUP BY clause is used to count the data which were grouped on a particular attribute of the table.
The GROUP BY statement is often used with aggregate functions ( COUNT() , MAX() , MIN() , SUM() , AVG() ) to group the result-set by one or more columns.
To count the number of rows, use the id column which stores unique values (in our example we use COUNT(id) ). Next, use the GROUP BY clause to group records according to columns (the GROUP BY category above). After using GROUP BY to filter records with aggregate functions like COUNT, use the HAVING clause.
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
112
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
23
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
