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 wondeful stackoverflow people I am learning why. I spent three hours to google here and there and try to study w3school for this.

>Solution :
Wrap your case expression query up in a derived table. GROUP BY its result:
select Region, age_group, count(*) freq
from
(
select Region,
case when Age>54 then 'old'
when Age<36 then 'young'
else 'mid' end as age_group
from dbo.tblCustomer
) dt
group by Region, age_group
order by Region, freq desc;
