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]
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