How to use count after using group by and count in sql?

Question

I am trying to see statistics of how many passenger passed from my application. After selecting this query;

select count(person_id), person_id from Passenger group by person_id;

count(person_id)    person_id
6                  123
2                  421
1                  542
3                  612
1                  643
2                  876

I see that passenger "123" passed 6 times. "421" passed 2 times. "542" passed 1 times etc.. So I want to make analyze and say that;

>     1 passenger passed 6 times,
>     2 passenger passed 2 times,
>     2 passenger passed 1 times,
>     1 passenger passed 3 times..

Here is sqlFiddle for your better understanding..

Tim Biegeleisen · Accepted Answer · 2015-05-06 08:44:19Z

2

You can use a SELECT with a subquery to obtain the result you want:

SELECT Concat(COUNT(*), ' passenger passed ', table.theCount, ' times,') FROM
(
    SELECT COUNT(person_id) AS theCount, person_id
    FROM Passenger
    GROUP BY person_id
) table
GROUP BY table.theCount

edited May 6, 2015 at 8:44

answered May 6, 2015 at 8:39

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

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

Comments

juergen d · Accepted Answer · 2015-05-06 08:38:36Z

1

select cnt, count(person_id)
from
(
  select count(person_id) as cnt, person_id 
  from Passenger 
  group by person_id
) tmp
group by cnt

answered May 6, 2015 at 8:38

juergen d

205k40 gold badges305 silver badges377 bronze badges

Comments

Darian · Accepted Answer · 2015-05-06 08:54:24Z

0

Is this what you are looking for?

select count(person_id) as "Num passengers", times
from (
  select count(person_id) as times, person_id
from Passenger 
group by person_id
) sub
group by times order by times ASC

answered May 6, 2015 at 8:54

Darian

3481 silver badge8 bronze badges

Collectives™ on Stack Overflow

How to use count after using group by and count in sql?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related