Mark Romano Mark Romano - 2 months ago 6
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.

Answer

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