count in sql - two tables

Question

I have two tables: teams(id, name) and users(id, name, team_id)

I want a following result:

Team  | Members 
team1 | 10 
team2 | 14 
team3 | 8

I tried:

SELECT t.name AS 'Team', COUNT(u.email) AS 'Members' FROM teams t INNER JOIN users u ON (u.team_id = t.id)

but it wouldn't work.

Blank · Accepted Answer · 2016-07-15 09:57:02Z

5

You should use GROUP BY, and you have a syntax error for alias, try this:

SELECT t.name AS `Team`, COUNT(u.email) AS `Members`
FROM teams t INNER JOIN users u ON (u.team_id = t.id)
GROUP BY t.name -- or t.id

answered Jul 15, 2016 at 9:57

Blank

12.4k1 gold badge16 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Arulkumar · Accepted Answer · 2016-07-15 09:57:39Z

0

You need to GROUP BY the teams name value:

SELECT t.name AS `Team`, 
       COUNT(u.email) AS `Members` 
FROM teams t 
INNER JOIN users u ON u.team_id = t.id
GROUP BY t.name

answered Jul 15, 2016 at 9:57

Arulkumar

13.3k14 gold badges51 silver badges76 bronze badges