I have an input data which looks like as shown below
Person_id Age
21352471 59
22157363 51
22741394 75
22764902 27
22771872 62
I am trying to calculate the frequency (no of patients) under each age group like 0-10, 11-20, 21-30 etc
Can help me as to how it can be done?
I was trying something like below by referring online but this doesn't help
select
person_id,
count(*) filter (where age<=10) as "0-10",
count(*) filter (where age>10 and age<=20) as "11-20",
count(*) filter (where age>20) as "21-30"
from
age_table
group by
person_id;
I expect my output to be like as shown below
Age_group freq
0-10 0
11-20 0
21-30 1
31-40 0
41-50 0
51-60 2
61-70 1
71-80 1
You can try the below -
select case when age<=10 then "0-10"
when age>10 and age<=20 then "11-20"
when age>20 and age<=30 then "21-30" end as age_group
count(person_id) as freq
from
age_table
group by
case when age<=10 then "0-10"
when age>10 and age<=20 then "11-20"
when age>20 and age<=30 then "21-30" end
here is a way to do this which splits the ages into batches of 10 as follows
select concat(
(age/10)*10
,'-'
,(age/10)*10+10
)as age_bracket
,count(person_id) as frequency
from t
group by concat(
(age/10)*10
,'-'
,(age/10)*10+10
)
order by 1
+-------------+-----------+
| age_bracket | frequency |
+-------------+-----------+
| 20-30 | 1 |
| 50-60 | 2 |
| 60-70 | 1 |
| 70-80 | 1 |
+-------------+-----------+
dbfiddle link
https://dbfiddle.uk/?rdbms=postgres_12&fiddle=720d56ae4428a3ddd25ecb5bdac3b7fa
Here is another answer if you are keen to show up all the age ranges from 0-100 regardless of if there were entries or not
with data
as (select concat(
case when x=1 then 0 else (x-1)*10+1 end
,'-'
,x*10
) as ranges
,case when x=1 then 0 else (x-1)*10+1 end lv
,x*10 as hv
from generate_series(1,10) x
)
select d.ranges as age_bracket
,count(t.person_id) as frequency
from data d
left join t
on t.age>=d.lv
and t.age<=d.hv
group by d.ranges
order by 1
-------------+-----------+
| age_bracket | frequency |
+-------------+-----------+
| 0-10 | 0 |
| 11-20 | 0 |
| 21-30 | 1 |
| 31-40 | 0 |
| 41-50 | 0 |
| 51-60 | 2 |
| 61-70 | 1 |
| 71-80 | 1 |
| 81-90 | 0 |
| 91-100 | 0 |
+-------------+-----------+
db fiddle link
https://dbfiddle.uk/?rdbms=postgres_12&fiddle=d21a1ef85d017a3d891ec6f85269a381