CODEWITHSUNDEEP
mysqlsqljoincount
Mike
Mike

Reputation: 105

count in sql - two tables

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.

Upvotes: 0

Views: 34

Answers (2)

Arulkumar
Arulkumar

Reputation: 13237

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

Upvotes: 0

Blank
Blank

Reputation: 12378

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

Upvotes: 5

Related Questions