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:

  1. Add the additional columns to the GROUP BY clause:

    GROUP BY Rls.RoleName, Pro.[FirstName], Pro.[LastName]
  2. 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.

  • "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..." is something what I was looking for.. Thnx
    – Irfan
    Aug 15 '17 at 11:46
  • NOTE: these two options can provide absolutely WRONG results! GROUP BY A,B,C can be totally different than what you want to get in comparison with GROUP BY A. and also, usually we can't use some aggregate function to get related column value. check this answer as a solution Dec 25 '18 at 11:00
  • I know that the two queries will give exactly same results in my case (always). But I want to know which is more efficient. Lets say Rolename is pk in one of the table being joined (so indexed) and the other columns are type varchar. basically what does max do here ? Apr 23 '20 at 3:33
  • @MohitSingh: I recommend asking a new question
    – Lukas Eder
    Apr 23 '20 at 8:43

Note: This answer is intended as a supplement to @Lukas Eder's answer

If there are multiple values present for the fields SELECTed but a field you wish to GROUP BY, you could instead grab the top matching line, rather than waiting for an aggregation (MAX) to return.

SELECT i.RoleName, i.*
(SELECT TOP 1 R.Rolename, Pro.FirstName, Pro.LastName FROM Rls as R JOIN Pro ON ... ) as i
     ON i.Rolename=Rls.RoleName

if you want ALL values to return in those other columns, but want to collapse it to a single entry see: https://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.