SQLTeam.com | Weblogs | Forums

How to create age groups from raw age and store in a new column?

I have column [Age] and i want to create categories and store in a column in the same table. Please suggest.

The table should look like below

Respondent  Age  Category
A    25    Under 30
B    55    51-60
C    67    Above 60

Select respondent , age ,
Case when age < 25 then 'under 30'
When age between 51 and 60 then '51 to 60'
When age > 60 then 'Above 60'
End as category
From table

You can add as many when clauses
As you want

Hope it helps

Don't create a column to store in the same table - create a table of age categories and use that in your queries.

Declare @ageCategories Table (
        MinAge int
      , MaxAge int
      , CategoryName varchar(30)
        );

 Insert Into @ageCategories
 Values (0, 29, 'Under 30')
      , (30, 50, '30 to 50')
      , (51, 60, '51 to 60')
      , (61, 999, '61 and over');

 Select *
   From (Values (25), (32), (52), (63))         a(Age)
  Inner Join @ageCategories                     c On a.Age Between c.MinAge And c.MaxAge

You can expand on this to include different sets of age categories - or additional descriptions, etc...