CODEWITHSUNDEEP
sql
Tri Nguyen Dung
Tri Nguyen Dung

Reputation: 959

Count rows of right table when joinning two tables using SQL Query

I have a question about SQL Query. Let me do the example to illustration my issue: I have two tables like this:

Roles Table

ID                    Role                        Role Description
1                     Administrator               Someone in administrator board
2                     User                        Someone who has an account
3                     Guess                       Someone who just view the website

Users Table

ID                  Username                 RoleID
1                    trind08                      1
2                    trind09                      1
3                    trind10                      1
4                    kimchi                       2
5                    linhchi                      2
6                    thanh01                      2
7                    thanh02                      3
8                    kiemanh                      3
9                    liemanh                      3

My issue is I want to view all roles and count the user who resolve to them.

Result table after running the query might look like this:

ID                Role                            Role Description                                Cound of User
1                 Administrator                   Someone in administrator board                  3
2                 User                            Someone who has an account                      3
3                 Guess                           Someone who just view the website               3

My first try to create a SQL Query like this:

select rol.*, usrCout as 'Count of User' from Roles rol
left join (select count(*) from Users where RoleID == rol.ID) usrCout;

But my query run unsuccessfully and I can't get the result I want. Please help me for this.

Thank you

Upvotes: 0

Views: 946

Answers (2)

bvr
bvr

Reputation: 4826

SELECT 
ID
,ROLE
,Role Description
,(SELECT COUNT(*) FROM Users where RoleID = rol.ID) AS UserCount
FROM Roles rol

Upvotes: 2

Meherzad
Meherzad

Reputation: 8553

Try this query

Select count(*) as 'Count of User', r.RoleID, Role, Role Description from role r, Users u where u.RoleId = r.Id group by r.RoleID,Role, Role Description;

Fiddle

Hope this helps

Upvotes: 1

Related Questions