SQL select case when and group-CodePudding

I have this table

I want to group by age with case and count the gender type

this case,

age <=20 then 'Group <=20'

age between 21-40 then 'Group 21-40'

age between 41-60 then 'Group 41-60'

age >60 then 'Group >60'

I've tried this code but it gives me an error

%%sql
select customer_id, birthdate, extract('year' from current_date) - extract('year' from birthdate ) age
case when age <= 20 then 'Group <= 20'
when age between 21 and 40 then 'Group 21 - 40'
when age between 41 and 60 then 'Group 41 - 60'
else 'Group > 60' end gender
from dim_customer 
group by 1

Any solution? thanks before

btw, I use this code in python

CodePudding user response：

Alias columns are not used in SELECT but it's used at GROUP BY and ORDER BY clause.

-- PostgreSQL
SELECT t.*
     , CASE WHEN age <= 20 THEN 'Group <= 20'
            WHEN age BETWEEN 21 AND 40 THEN 'Group 21 - 40'
            WHEN age BETWEEN 41 AND 60 THEN 'Group 41 - 60'
            ELSE 'Group > 60' END gender 
FROM (SELECT customer_id, birthdate
           , extract('year' from current_date) - extract('year' from birthdate ) age
from dim_customer) t

Please check from url https://dbfiddle.uk/?rdbms=postgres_11&fiddle=38c6ba05adc779aed3063e490bcc6376

N.B.: Use date_part('year', current_timestamp :: DATE) - date_part('year', birthdate) for age calculation.

Count gender and use alias at group by section

SELECT CASE WHEN age <= 20 THEN 'Group <= 20'
            WHEN age BETWEEN 21 AND 40 THEN 'Group 21 - 40'
            WHEN age BETWEEN 41 AND 60 THEN 'Group 41 - 60'
            ELSE 'Group > 60' END gender
        , COUNT(t.gender) count_gender
        , COUNT(CASE WHEN t.gender = 'M' THEN 1 END) gen_male
        , COUNT(CASE WHEN t.gender = 'F' THEN 1 END) gen_female
FROM (SELECT customer_id, birthdate, gender
           , extract('year' from current_date) - extract('year' from birthdate ) age
           , date_part('year', current_timestamp :: DATE) - date_part('year', birthdate) age1
from dim_customer) t
GROUP BY CASE WHEN age <= 20 THEN 'Group <= 20'
            WHEN age BETWEEN 21 AND 40 THEN 'Group 21 - 40'
            WHEN age BETWEEN 41 AND 60 THEN 'Group 41 - 60'
            ELSE 'Group > 60' END

Please check from url https://dbfiddle.uk/?rdbms=postgres_11&fiddle=ad8af9dd9b82ede20452377615ca7fde