Group by aggregate function SQL

Question

I need to group by an aggregated by count column and it's not possible on SQL. As a simple example, I have a table with events and I want to group by the count of those events.

Table (event 1,event 2,event 2,event 3,event 3,event 4)

select events, count (events) 
from table 
group by events

the result is

events   count(events)
event 1  1
event 2  2
event 3  2
event 4  1

What I need is to know how many events occurred 1 time and how many 2 times and so on, which would give me the following result:

count(events) occurences
1             2
2             2

Can anyone suggest a way to obtain that result, please?

Thanks in advance!

question from:https://stackoverflow.com/questions/65905477/group-by-aggregate-function-sql

Answer 1

here is one way you can do it:

select 
   e.counts
 , count(*) occurences 
from (
select events, count (events) counts
from table 
group by events
) e
group by e.counts