SQL , getting invalid column name error for a piece of code working perfectly online [duplicate]

Question

This is my table :

I am taking an online course on Udemy in SQL, the video has this code and it is running. I do not know why I get error below when I run it. I appreciate any help. the code is supposed to return the number of each age group in each region.

select * from dbo.tblCustomer;
select Region,   
       case when Age>54 then 'old'
            when Age<36 then 'young'
            else 'mid' end as age_group
        ,count(*) as freq
from dbo.tblCustomer 
group by Region, age_group
order by Region, freq desc;

Msg 207, Level 16, State 1, Line 43
Invalid column name 'age_group'.
Msg 207, Level 16, State 1, Line 44
Invalid column name 'count'.

PS. I took $9.9 courses on Udemy, this is a screen shot of the video I'm learning SQL from. his codes, mine does not. Now with help wonderful StackOverflow people I am learning why. I spent three hours to google here and there and try to study W3Schools for this.

Answer 1

You can't refer to a column alias in the same query in the way you are doing due to the order of execution.

You either need to repeat the expression in the group-by or you can use a CTE or Derived Table, where you can then reference them. You should also be able to materialize your case expression using apply:

select Region, age_group, Count(*) as freq
from dbo.tblCustomer 
cross apply(
    values(
        case when Age>54 then 'old'
             when Age<36 then 'young'
             else 'mid'
        end
    )
)v(age_group)
group by Region, age_group
order by Region, Count(*) desc;