How to calculate age from date of birth and group each member into age range in sql

Question

I have an sql table that stores people's details i.e id, name, DoB, registration_date and address. I would like to calculate the age of each individual and then group them into these ranges: 20-30, 31-50, 51 & over.

I know i can get the age by doing: (https://stackoverflow.com/a/1572257/3045800)

SELECT FLOOR((CAST (GetDate() AS INTEGER) - CAST(Date_of_birth AS INTEGER)) / 365.25) AS Age

I just need to figure out how to group all people into thier respective range.

Thanks for the help

Answer 1

Use a case to produce the age group description:

select *,
  case
    when datediff(now(), date_of_birth) / 365.25 > 50 then '51 & over'
    when datediff(now(), date_of_birth) / 365.25 > 30 then '31 - 50'
    when datediff(now(), date_of_birth) / 365.25 > 19 then '20 - 30'
    else 'under 20'
  end as age_group
from person

Note the simpler way to calculate age.

Answer 2

You can use with construction:

with Query as (
   select FLOOR((CAST (GetDate() AS INTEGER) - CAST(Date_of_birth AS INTEGER)) / 365.25) AS Age
          ... -- Other fields
     from MyTable
   )

   select case 
            -- whatever ranges you want
            when (Age < 20) then
              1
            when (Age >= 20) and (Age <= 30) then
              2
            when (Age > 30) and (Age <= 50) then
              3
            else
              4  
          end AgeRange,
          ...  
     from Query
 group by AgeRange