0

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]
Faraz salehi
  • 328
  • 2
  • 17

1 Answers1

1

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
Salman A
  • 262,204
  • 82
  • 430
  • 521
  • do you know how to make contact to columns in one column ... like (users.first_name, users.last_name) AS full name? – Faraz salehi Dec 08 '21 at 13:29
  • `concat_ws(' ', users.first_name, users.last_name)`. – Salman A Dec 08 '21 at 13:30
  • im trying to store this result like this link :https://stackoverflow.com/questions/10070406/how-to-store-query-result-in-variable-using-mysql/23282102 .. but it wont work – Faraz salehi Dec 08 '21 at 14:53