Counting in sql and subas

Question

I have the following code

select ID, count(*) from 
( select ID, service type from database
group by 1,2) suba 
group by 1 
having count (*) > 1

And I get a table where i see the IDs and a count of changes. Similar to this

ID     |   Count(*)
5675   |   2
5695   |   3
5855   |   2
5625   |   4
5725   |   3

Can someone explain to me how to count all the count(*) into groups such that i get a table similar to...

count (*)   | number
2 | 2
3 | 2
4 | 1

and so forth. Can someone also explain to be me what suba means?

MY NEWEST CODE:

select suba.id, count(*) from 
( select id, service_type from table_name
group by 1,2) as  suba
group by 1 
having count (*) > 1 

Answer 1

Haven't tried it, but I think this should work

 select NoOfChanges, count (*) from
 ( 
     select suba.id, count(*) as NoOfChanges from 
      ( select id, service_type from table_name
       group by 1,2) as  suba
       group by 1 
       having count (*) > 1 
    )
 subtableb
 group by NoOfChanges 

You can think of that as

select NoOfChanges, count (*) from subtableb
group by NoOfChanges  

but subtableb isn't a real table, but the results from your previous query

Answer 2

suba is the alias of the subquery. Every table or subquery needs a unique name or an alias so you can refer to it in other parts of the query (and disambiguate). Note there is a missing implicit AS between the closing parenthesis and "suba".