Postgres : using computed variable in a SELECT statement part 1/2-CodePudding

I'm having a SELECT statement as follow (doesn't work):

SELECT 
    extract(day from CAST (date as TIMESTAMP) - CAST (birth_date as TIMESTAMP)) / 365.25 as age_norm,
    CASE 
       WHEN age_norm >= 0 AND age_norm <1 THEN '00'
       WHEN age_norm >= 1 AND age_norm <5 THEN '01-4'
       --etc
    END as age_group
FROM foo

Is there a way to "inject" here the "variable" age_normin the query ?

EDIT:

Asked a similar question here, but this time with an additional column in the SELECT statement, which is the use case I'm trying to solve

CodePudding user response：

We can calculate the age_norm column in a subquery and then use age_norm by CASE WHEN in the main query.

SELECT 
    age_norm,
    CASE 
       WHEN age_norm >= 0 AND age_norm <1 THEN '00'
       WHEN age_norm >= 1 AND age_norm <5 THEN '01-4'
       --etc
    END as age_group
FROM (
   SELECT extract(day from CAST (date as TIMESTAMP) - CAST (birth_date as TIMESTAMP)) / 365.25 as age_norm
   FROM foo
) t1