Getting count of different values ​​in mySql

Question

I have two tables for teams for players. What I'm trying to figure out is a general counting table. In other words, I want to have the total number of teams in which there are 2 participants, for all the number of teams that have 3 participants, etc.

Here is the database structure.

(Sidebar Question: I'm new here: is there a better way to publish SQL?)

CREATE TABLE `formsfiles`.`Teams` ( `ID` INT NOT NULL AUTO_INCREMENT , `Name` VARCHAR(45) NULL , PRIMARY KEY (`ID`) ); INSERT INTO `Teams` (`Name`) VALUES ('Sharks'); INSERT INTO `Teams` (`Name`) VALUES ('Jets'); INSERT INTO `Teams` (`Name`) VALUES ('Fish'); INSERT INTO `Teams` (`Name`) VALUES ('Dodgers'); CREATE TABLE `Players` ( `ID` INT NOT NULL AUTO_INCREMENT , `Name` VARCHAR(45) NULL , `Team_ID` INT NULL , PRIMARY KEY (`ID`) ); INSERT INTO `Players` (`Name`, `Team_ID`) VALUES ('Jim', '1'); INSERT INTO `Players` (`Name`, `Team_ID`) VALUES ('Tom', '1'); INSERT INTO `Players` (`Name`, `Team_ID`) VALUES ('Harry', '2'); INSERT INTO `Players` (`Name`, `Team_ID`) VALUES ('Dave', '2'); INSERT INTO `Players` (`Name`, `Team_ID`) VALUES ('Tim', '3'); INSERT INTO `Players` (`Name`, `Team_ID`) VALUES ('Trey', '4'); INSERT INTO `Players` (`Name`, `Team_ID`) VALUES ('Jay', '4'); INSERT INTO `Players` (`Name`, `Team_ID`) VALUES ('Steve', '4'); INSERT INTO `Players` (`Name`, `Team_ID`) VALUES ('Chris', '4'); 

What I want is the number of teams in the group.

I would like to see the following output

 Team_Size Count 1 1 2 2 4 1 

Answer 1

The easiest way:

 select team_count, count(*) from (select count(*) team_count from players group by team_id) sq group by team_count 

(Although this will not include teams without players in them.)

SQLFiddle here .

Answer 2

First you need the size of the command:

 select t.id as teamId, count(p.id) as teamSize from `Teams` as t left join `Players` as p on t.id = p.teamId group by t.id; 

Please note that this will return teams with zero players. If you do not want this, use inner join instead of left join .

Now use this query as the source of the string for your final query:

 select teamSize, count(teamId) from ( select t.id as teamId, count(p.id) as teamSize from `Teams` as t left join `Players` as p on t.id = p.teamId group by t.id) as a group by teamSize; 

Hope this helps

---

One more thing.

If you have large datasets, this query may freeze. Therefore, it is best to create a temporary table, index it, and run a query in the temp table:

 drop table if exists temp_teamSize; create temporary table temp_teamSize select t.id as teamId, count(p.id) as teamSize from `Teams` as t left join `Players` as p on t.id = p.teamId group by t.id; alter table temp_teamSize add unique index idx_teamId(teamId), add index idx_teamSize(teamSize); select teamSize, count(teamId) from temp_teamSize group by teamSize;