CODEWITHSUNDEEP
mysqlcount
goGud
goGud

Reputation: 4333

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

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..

Upvotes: 2

Views: 104

Answers (3)

Darian
Darian

Reputation: 348

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

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521289

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

Upvotes: 2

juergen d
juergen d

Reputation: 204766

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

Upvotes: 1

Related Questions