Mark Romano - 18 days ago 4
R Question

# R: Create groups within column

I'm trying to group an age column into an age group column and summarize by that grouping.

ie I need the dataset below -

``````AGE
1
2
5
68
27
4
2
33
45
``````

To become

``````AGE_GRP COUNT
1-10     5
11-20    0
21-30    1
31-40    1
40+      2
``````

I'm using R

Thanks.

You need `CASE` statement to split the `AGE` into different groups

``````SELECT CASE
WHEN AGE BETWEEN 1 AND 10 THEN '1-10'
WHEN AGE BETWEEN 11 AND 20 THEN '11-20'
WHEN AGE BETWEEN 21 AND 30 THEN '21-30'
WHEN AGE BETWEEN 31 AND 40 THEN '31-40'
ELSE '40+'
END AS AGE_GRP,
Count(1) as Cnt
FROM   yourtable
GROUP  BY CASE
WHEN AGE BETWEEN 1 AND 10 THEN '1-10'
WHEN AGE BETWEEN 11 AND 20 THEN '11-20'
WHEN AGE BETWEEN 21 AND 30 THEN '21-30'
WHEN AGE BETWEEN 31 AND 40 THEN '31-40'
ELSE '40+'
END
``````

If you don't want to repeat the `CASE` statement in `GROUP BY` then use this

``````SELECT AGE_GRP,
Count(1) AS cnt
FROM   (SELECT CASE
WHEN AGE BETWEEN 1 AND 10 THEN '1-10'
WHEN AGE BETWEEN 1 AND 10 THEN '11-20'
WHEN AGE BETWEEN 1 AND 10 THEN '21-30 '
WHEN AGE BETWEEN 1 AND 10 THEN '31-40'
ELSE '40+'
END AS AGE_GRP
FROM   yourtable) A
GROUP  BY AGE_GRP
``````
Source (Stackoverflow)