Search code examples
mysqlsqlmysql-workbenchsequelpro

need help to find number of times a number is repeated in query result

I have a table like following 

item_id   link_id
1         10
1         20
2         100
2         40
2         10
3         10
3         30
4         10
4         20
4         30

I ran the query to find occurrence of each item_id

select `item_id`, count(`item_id`)
from `table`
group by `order_id`

which gave me the result 

item_id   count('item_id')
1         2
2         3
3         2
4         3

But I have to find out how many time did i had each value in result, something like this 

count('item_id')   Occurence
2                  2
3                  2

How should I update the query

Solution

  • Use two levels of aggregation:

    select cnt, count(*), min(item_id), max(item_id)
from (select `item_id`, count(`item_id`) as cnt
      from `table`
      group by `order_id`
     ) i
group by cnt;

    I also often add min(item_id), max(item_id) to such queries to get examples for each count.