Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

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

This is my table : enter image description here

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’.

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

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.
enter image description here

>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;
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading