get count of each users role-CodePudding

I want to get each user role count, in the user-role table.

I can get the user's name with the role name in a result but I don't know how to get count of roles for each user.

my query:

SELECT users.first_name,
roles.name FROM user_roles
JOIN users
on users.id = user_roles.user_id
JOIN roles
on roles.id = user_roles.role_id;

#Database Structure:

roles => [id,name]
users => [id,first_name,last_name]
user_roles => [user_id,role_id]

CodePudding user response：

Just add a GROUP BY and COUNT:

SELECT users.first_name, COUNT(roles.id) AS role_count
FROM user_roles
JOIN users on users.id = user_roles.user_id
JOIN roles on roles.id = user_roles.role_id
GROUP BY users.id