How can i use group by clause on case statement aliasing in tsql?

Question

I have a case statement in my query with a aliasing and im trying to display it with group-by clause but i keep on getting the error "Invalid Column Name (case statement aliasing)"

Code

Select column 1,
       column 2,
       column 3,
       Case When .... then ...
            When .... then ...
            When .... then ... 
            else ... as X
From table1,
     table 2,
     table 3
where condition 1,
      condition 2,
      condition 3
group by column 1,
         column 2,
         X,
         column 3;

Output : Invalid Column name 'X'.

Either repeat the case statement, use a derived table or a CTE — Stu, Apr 27 '22 at 14:49
`CASE` **expression** by the way; T-SQL doesn't support `Switch` (`Case`) statements. — Thom A, Apr 27 '22 at 14:51
@Larnu thanks - I know the terminology :) just had a lax moment. — Stu, Apr 27 '22 at 15:04

score 0 · Accepted Answer · answered Apr 27 '22 at 14:52

You need to include the whole case statement in the group by clause instead of using the alias. So

group by column 1,
         column 2,
       Case When .... then ...
            When .... then ...
            When .... then ... 
            else ... ,
         column 3;

How can i use group by clause on case statement aliasing in tsql?

1 Answers1