Mike Mike - 4 months ago 7
MySQL Question

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.

Answer

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