SELECT UR.[Id] ,UR.[Name] ,COUNT(ur.name) FROM [dbo].[UserRole] UR INNER JOIN dbo.[User] U ON U.Role = UR.Id GROUP BY UR.Id, ur.Name
All information on this site is shared with the intention to help. Before any source code or program is ran on a production (non-development) system it is suggested you test it and fully understand what it is doing not just what it appears it is doing. I accept no responsibility for any damage you may do with this code.