Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
insanepaul
Posting Yak Master
178 Posts |
 Posted - 2010-05-18 : 12:10:18
|
| Hi,I'm getting lost trying to count the number of Users(or rows) that have a particular role in a table. There are 2 tables:UserRoleuserId.........roleIdRoleroleId.........parentRoleId...........roleNameI need to get the 'Other' roleName in the 'Role' table and then get its parentRoleId to find all the roles that have the same parentroleid (there were about 10). But how do I count them and display them like this:Professor.......10Nurse...........18So far i've done this which returns a list of userid's and roleid's:select * from userrole where roleid in (select roleid from [role] where parentroleid = (select parentroleid from [role] where role = 'Other')) |
|
|
Nikhil1home
Starting Member
23 Posts |
Posted - 2010-05-18 : 12:15:57
|
quote:
Originally posted by insanepaulI think I need to get the 'Other' role in 'Role' and then get its parentRoleId to find all the roles that have the same parentroleid (there were about 10). But how do I count them and display them like this:Professor.......10Nurse...........18
This is not clear what you are trying to do. Do you sample data and expected output? |
 |
|
|
insanepaul
Posting Yak Master
178 Posts |
Posted - 2010-05-18 : 12:20:19
|
quote:
Originally posted by Nikhil1home
quote:
Originally posted by insanepaulI think I need to get the 'Other' role in 'Role' and then get its parentRoleId to find all the roles that have the same parentroleid (there were about 10). But how do I count them and display them like this:Professor.......10Nurse...........18
This is not clear what you are trying to do. Do you sample data and expected output?
Hi, Thanks for the quick reply. I just need an sql command that displays the name of the role and the number of users that have that role. |
|
|
|
insanepaul
Posting Yak Master
178 Posts |
Posted - 2010-05-18 : 12:29:10
|
quote:
Originally posted by Nikhil1home
quote:
Originally posted by insanepaulI think I need to get the 'Other' role in 'Role' and then get its parentRoleId to find all the roles that have the same parentroleid (there were about 10). But how do I count them and display them like this:Professor.......10Nurse...........18
This is not clear what you are trying to do. Do you sample data and expected output?
Not sure what you mean by sample data and output...I can get the rolenames using the sql code in the first post but I don't know how to count the number of rows each rolename has in the table. |
|
|
|
insanepaul
Posting Yak Master
178 Posts |
Posted - 2010-05-18 : 12:35:31
|
quote:
Originally posted by Nikhil1home
quote:
Originally posted by insanepaulI think I need to get the 'Other' role in 'Role' and then get its parentRoleId to find all the roles that have the same parentroleid (there were about 10). But how do I count them and display them like this:Professor.......10Nurse...........18
This is not clear what you are trying to do. Do you sample data and expected output?
That's excellent thanks. (I saw your code but the post was deleted or something - weired!)) |
|
|
|
Nikhil1home
Starting Member
23 Posts |
Posted - 2010-05-18 : 12:36:54
|
| The following query assumes that there is only 1 record per unique RoleID in the Role table and not two RoleIDs have the same RoleNames. If that's the case, it should work. We don't know your data that's why asked you to post a few relevant rows in both the tablesSELECT RoleName, COUNT(*) AS UserCountFROM [Role] JOIN UserRole ON [Role].RoleID = UserRole.RoleIDwhere [role].roleid in (select roleid from [role] where parentroleid = (select parentroleid from [role] where role = 'Other'))GROUP BY RoleName |
|
|
|
|
|
|
counting number of rows