SQL: How do i rename an SQL case statement and still be able to group it-CodePudding

SELECT COUNT(median_age) 'Frequency',  
    CASE  
        WHEN median_age < 20 THEN 'Terrible'  
        WHEN median_age < 30 THEN 'Low'  
        WHEN median_age < 40 THEN 'Medium'  
        ELSE 'High'  
    END Life_Expectancy    
FROM countries  
GROUP BY Life_Expectancy    
ORDER BY median_age;

Sorry in advance if this has been asked before im new to this site and SQL. The Above code works perfectly however my column name is Life_expectancy. I can change the END line to 'Life Expectancy' which is my aim. However when i do this i can no longer group my data in the GROUP BY line. If i try to group by 'Life Expectancy' it simply averages all my data which isnt very usefull.

Desired output: Currently acheived with above code
Frequency Life_Expectancy
32 Terrible
63 Low
57 Medium
49 High

Output if i change it to 'Life Expectancy':
Frequency Life Expectancy
201 Medium

CodePudding user response：

Escape the alias name in quotes:

SELECT COUNT(median_age) 'Frequency',  
    CASE WHEN median_age < 20 THEN 'Terrible'  
         WHEN median_age < 30 THEN 'Low'  
         WHEN median_age < 40 THEN 'Medium'  
         ELSE 'High'  
    END AS "Life Expectancy"
FROM countries  
GROUP BY 2   
ORDER BY
    CASE "Life Expectancy" WHEN 'Terrible' THEN 1
                           WHEN 'Low' THEN 2
                           WHEN 'Medium' THEN 3
                           ELSE 4 END;

The ugliness of the ORDER BY clause has to do with that the only things which can appear there are the count and the aliased Life Expectancy. So using my approach I was forced to derive another CASE expression to generate the desired ordering.