How to select multiple columns but only group by one?

Question

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;

Answer 1

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.

Answer 2

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.*
FROM Rls JOIN 
(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/

Answer 3

I had this same problem when I wanted to group by something from a transactional table where the timestamp was the latest entry. So for example an audit table that had a username, someUniqueId and a timestamp.

So I could get the max timestamp of when all the objects were last updated via:

  SELECT max(timeStamp) AS timeStamp, someUniqueId 
  FROM tableName
  GROUP BY someUniqueId 

But then I couldn't pull out any other columns, ie, the username... I managed to solve this by selecting from the main table and using the above result set in an inner join like so:

  SELECT a.someUniqueId, a.timeStamp, a.username
  FROM tableName a
  INNER JOIN (
    SELECT max(timeStamp) AS timeStamp, someUniqueId 
    FROM tableName
    GROUP BY someUniqueId 
  ) b ON a.someUniqueId = b.someUniqueId AND a.timeStamp = b.timeStamp