Why cant I use GROUP BY in this Query

Question

When i Executed the following query in MS SQL Management Studio it produced an error.

Query:

select  u.UserName, r.RoleName
from User u
join UserRole ur on ur.UserId = u.UserId
join Role r on r.RoleId = ur.RoleId
group by r.RoleName

Error:

Msg 8120, Level 16, State 1, Line 2 Column 'User.UserName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Note:

There is no error if I use

group by r.RoleName, u.UserName

Answer 1

This demonstrates the single-value rule, which prohibits the undefined results you get when you run a GROUP BY query, and you include any columns in the select-list that are neither part of the grouping criteria, nor appear in aggregate functions (SUM, MIN, MAX, etc.). Hope u understand.

Group By

I dont know but may be you want this..

select distinct u.UserName, r.RoleName
from User u
join UserRole ur on ur.UserId = u.UserId
join Role r on r.RoleId = ur.RoleId