How to select multiple columns but only group by one?

I have a problem with group by, I want to select multiple columns but group by only one column. The query below is what I tried, but it gave me an error.

SELECT Rls.RoleName,Pro.[FirstName],Pro.[LastName],Count(UR.[RoleId]) as [Count]
from [b.website-sitecore-core].[dbo].[aspnet_UsersInRoles] UR
inner join [b.website-professional-au].[dbo].[Profile]  Pro
on UR.UserId = Pro.Id
inner join [b.website-sitecore-core].[dbo].[aspnet_Roles] Rls
on Rls.RoleId = UR.RoleId
inner join [b.website-professional-au].[dbo].[Gender] Gn
on gn.Id = pro.GenderId
GROUP BY Rls.RoleName;

In SQL Server you can only select columns that are part of the GROUP BY clause, or aggregate functions on any of the other columns. I've blogged about this in detail here. So you have two options:

-
Add the additional columns to the GROUP BY clause:

GROUP BY Rls.RoleName, Pro.[FirstName], Pro.[LastName]

-
Add some aggregate function on the relevant columns:

SELECT Rls.RoleName, MAX(Pro.[FirstName]), MAX(Pro.[LastName])

The second solution is mostly a workaround and an indication that you should fix something more general with your query.

StackExchange Database Administrators Q#71887, answer score: 24