Is group by good for this use case?-CodePudding

I have a table that has user_id and role_id. I want to group user_id: 1 which also can multiple roles and In the end, I want to show count. Like for this user_id, there are 7 roles.

How can I achieve this in raw SQL query?

CodePudding user response：

SELECT user_id, count(role_id) c
FROM atable
GROUP BY user_id

CodePudding user response：

It seems that you are looking for typical group by:

 select user_id, 
        count(role_id) 
   from MyTable

group by user_id;

here we group all records within MyTable by their user_id and then count all non null roled_id within each group. Depending on how role_id should be count you may want to put

  count(all role_id)

to count null role_id as well as not null ones

  count(distinct role_id)

to count distinct role_id in each group