CODEWITHSUNDEEP
mysql
TSCAmerica.com
TSCAmerica.com

Reputation: 5377

Mysql - Display Team name and Count of Team members from 2 tables

The following query works great 

SELECT
          t.name, 
          t.id
        FROM
          team t,
          member m
        WHERE
          m.team_id = t.id

and shows multiple results what I am stuck with it is with how to modify the query about to display the team name and the number of team members in that team so, for example, Team A has 50 team members, Team B has 20 members and so on.

The problem is that the member.team_id has Comma separated values

enter image description here

My table structure for team table

enter image description here

My table structure for member table

enter image description here

Not a duplicate question at all

Upvotes: 0

Views: 1073

Answers (2)

Barmar
Barmar

Reputation: 780909

Use the technique in sql join tables where 1 column has comma to join the tables, then use COUNT(*) to get the member counts.

SELECT t.name, COUNT(*) 
FROM team t 
JOIN member m 
ON FIND_IN_SET( m.team_id, t.id ) > 0 
GROUP BY t.name

Upvotes: 1

Paul Maxwell
Paul Maxwell

Reputation: 35583

To get the number of members from comma separated list try using length() and replace()

select
(LENGTH(team_ids) - LENGTH(REPLACE(team_ids, ',', '')))+1
from MyTable

By removing the commas the length is reduced by the number of those, and ou need 1 more because there isn't a trailing comma at the end.

Upvotes: 0

Related Questions