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

June 21, 2022

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

>Solution :

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