sql - Get number of players in the team and city

Question

I have 2 tables. Player and Team. Team = name, city. Player = playerID, team

Question: Number of players in each team and city

I'm finding number of player in city. For example;

SELECT COUNT(*),city
FROM Team t INNER JOIN Player p
ON t.name=p.team
GROUP BY city

Output;

City, number of player in city
A   , 10
B   , 5
C   , 15

I'm finding number of player in team. For Example;

SELECT COUNT(*), team
FROM Player
GROUP BY team

Output;

Team, number of player in team
A1  , 5
A2  , 2
A3  , 3
B1  , 2
B2  , 3
C1  , 15

But I want to get these values in the same table. Output;

City, Team, number of player in team, number of player in city
A   , A1  , 5  , 10
A   , A2  , 2  , 10
A   , A3  , 3  , 10
B   , B1  , 2  ,  5
B   , B2  , 3  ,  5
C   , C1  , 15 , 15

question from:https://stackoverflow.com/questions/65944609/get-number-of-players-in-the-team-and-city

Answer 1

Assuming a team can only exist in one city, the granularity of the team is the same whether you add the city or not. So it would be:

with PlayerCountByCity as
(
SELECT t.City, COUNT(*) as CityPlayerCount
FROM Team t INNER JOIN Player p
ON t.name=p.team
GROUP BY t.City
), PlayerCountByTeam as
(
SELECT t.City, p.Team, COUNT(*) as TeamPlayerCount
FROM Team t inner join Player p
on t.name=p.team
GROUP BY t.City, t.Team
)
Select a.City, b.Team, b.TeamPlayerCount, a.CityPlayerCount
from PlayerCountByCity a inner join PlayerCountByTeam b on a.City=b.City

If you had a city table it would preferable because you could use the City table to federate over Teams with no players and Cities with no teams the final query would look like:

Select a.City, c.Team, c.TeamPlayerCount, b.CityPlayerCount
from City a left join PlayerCountByCity b on a.City=c.City 
left join PlayerCountByTeam c on a.City=c.City