How to get additional column with row count using two different tables?-CodePudding

In my database I have usergroup, usergroup_user tables. I want to make a SQL query which can result something like result(group_id, name, date, users_count).

usergroup table

------------------
| group_id| name | 
------------------
|  10     |test1 |
|  11     |test2 |
|  12     |test3 | 
------------------

usergroup_user table

---------------------
|group_id | user_id | 
---------------------
|  10     |  100    |
|  10     |  200    |
|  10     |  250    | 
|  11     |  250    | 
|  11     |  700    | 
---------------------

I want to get this kind of a reusult

------------------------------
|group_id | name |users_count| 
------------------------------
|  10     |test1 |  3        | 
|  11     |test2 |  2        |  
|  12     |test3 |  0        | 
------------------------------

CodePudding user response：

You simply do this with the group by, as per the following bellow.

SELECT U.group_id ,U.RoleName,COUNT(R.Id)USERCOUNT 
FROM usergroup U
LEFT OUTER JOIN usergroup_user R ON R.group_id =U.group_id 
GROUP BY  U.group_id ,U.RoleName