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!

Please, tag your DBMS

astentx
– astentx

2021-01-26 16:52:13 +00:00
Commented Jan 26, 2021 at 16:52 — astentx
– astentx, Commented Jan 26, 2021 at 16:52

eshirvana · Accepted Answer · 2021-01-26 16:47:10Z

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

answered Jan 26, 2021 at 16:47

eshirvana

24.7k3 gold badges28 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

astentx Over a year ago

This is the most straightforward way for almost any DBMS, I think

Collectives™ on Stack Overflow

Group by aggregate function SQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related