CODEWITHSUNDEEP
sqlsqlitecount
Nima
Nima

Reputation: 1540

Count repeated group values in sql

I have a table with repeated records of individuals who have unique p_id who are member of different sites which have s_id. How can I get number of sites with X number of unique p_id?

So currently I have

p_id  name   s_id
----  ----   ----
1     John   01
2     Alex   01
3     Ben    02
4     Mark   02
5     Colin  02
etc

and I would like to use SQL to produce this table:

Site with 1 people: 0
Sites with 2 people: 1
Sites with 3 people: 3

I thought of doing this:

select p_id, s_id, s_count from 
  (select p_id, s_id, count(*) as s_count from mytable group by s_id)

but I don't think it's correct and there might be a better way - any ideas?

In case anyone is confused - I am looking for the sql statement to get the count, not anything else.

Upvotes: 1

Views: 118

Answers (1)

CL.
CL.

Reputation: 180270

First, count the number of people per site:

SELECT s_id,
       COUNT(*) AS NumberOfPeople
FROM ATable
GROUP BY s_id

This outputs one row per site.

Then, group again, but by the previously computed count column:

SELECT NumberOfPeople,
       COUNT(*) AS NumberOfSites
FROM (SELECT s_id,
             COUNT(*) AS NumberOfPeople
      FROM ATable
      GROUP BY s_id)
GROUP BY NumberOfPeople

Upvotes: 3

Related Questions