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

Question

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

D-Shih · Accepted Answer · 2022-06-21 10:19:00Z

1

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

answered Jun 21, 2022 at 10:19

D-Shih

46.5k6 gold badges37 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Hey StackExchange Over a year ago

Thank you. It works. Refined the question here (stackoverflow.com/questions/72699395/…), as I have another column in the select statement as mentionned in the edit of the initial post.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related