The column is not valid in the select list because it is neither contained in the aggregate function nor in the GROUP BY clause

Question

I have a sql query below, but I am facing a problem while executing it.

SELECT * from (Select row_number() OVER(Order By FloorUserId) as 'row_number', FloorUserId, max(CASE WHEN AreaId='[G]' or AreaId=N'L01' THEN 'X' ELSE ' ' END) as 'L01', max(CASE WHEN AreaId='[G]' or AreaId=N'L02' THEN 'X' ELSE ' ' END) as 'L02' from floor, tbuser where FloorUserId= tbuser.userID ) as derivedTable where row_number BETWEEN 1 AND 20 

But I keep getting the following error:

The column "FloorId" is invalid in the selection because it is not contained in either the aggregate function or the GROUP BY clause.

Answer 1

• You have MAX, which is designed for aggregates, so you will need GROUP BY Id
• ... this will not work because you have ROW_NUMBER
• Do you really want a Cartesian product (CROSS JOIN) between gender and user?
• which column belongs to which table?

Perhaps this will help you get where you want to:

 Select row_number() OVER (PARTITION BY userid Order By user.Id) as 'row_number', user.Id, max(CASE WHEN floor.AreaId='[G]' or floor.AreaId=N'L01' THEN 'X' ELSE ' ' END) as 'L01', max(CASE WHEN floor. AreaId='[G]' or floor.AreaId=N'L02' THEN 'X' ELSE ' ' END) as 'L02' from floor JOIN user ON floor. = user. --what? where user.Id = userID group by user.Id 

Answer 2

You can only use fields that are part of a group by clause when using aggregates (e.g. max).

So get rid of '*' if you want other fields to add them to the group by clause.