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

Question

I have a sql table that stores face data ie id, name, DoB, registration_date and address. I would like to calculate the age of each person and then group him into these ranges: 20-30, 31-50, 51 and more.

I know that I can get the age by doing: ( https://stackoverflow.com/a/14610/ ... )

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 the appropriate range.

thanks for the help

Answer 1

Use caseto create an 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

Pay attention to an easier way to calculate age.

Answer 2

:

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

Answer 3

I once came across the same requirement, and this is how I fixed it: I am sorry that there was no direct way, since this is not:

  SELECT  (CASE
              WHEN G.DATE_OF_BIRTH IS NULL
              THEN
                 '18-24' --Put your default Range In case the date of birth is null
              ELSE
                 CASE
                    WHEN   EXTRACT (
                              YEAR FROM (select sysdate from dual))
                         - EXTRACT (YEAR FROM g.DATE_OF_BIRTH) < 18
                    THEN
                       'MINORS'
                    ELSE
                       CASE
                          WHEN   EXTRACT (
                                    YEAR FROM (select sysdate from dual))
                               - EXTRACT (YEAR FROM g.DATE_OF_BIRTH) BETWEEN 25
                                                                         AND 29
                          THEN
                             '25-29'
                          ELSE
                             CASE
                                WHEN   EXTRACT (
                                          YEAR FROM (select sysdate from dual))
                                     - EXTRACT (YEAR FROM g.DATE_OF_BIRTH) BETWEEN 30
                                                                               AND 34
                                THEN
                                   '30-34'
                                ELSE
                                   CASE
                                      WHEN   EXTRACT (
                                                YEAR FROM (select sysdate from dual))
                                           - EXTRACT (
                                                YEAR FROM g.DATE_OF_BIRTH) BETWEEN 35
                                                                               AND 39
                                      THEN
                                         '35-39'
                                      ELSE
                                         CASE
                                            WHEN   EXTRACT (
                                                      YEAR FROM (select sysdate from dual))
                                                 - EXTRACT (
                                                      YEAR FROM g.DATE_OF_BIRTH) BETWEEN 40
                                                                                     AND 49
                                            THEN
                                               '40-49'
                                            ELSE
                                               CASE
                                                  WHEN   EXTRACT (
                                                            YEAR FROM (select sysdate from dual))
                                                       - EXTRACT (
                                                            YEAR FROM g.DATE_OF_BIRTH) BETWEEN 50
                                                                                           AND 59
                                                  THEN
                                                     '50-59'
                                                  ELSE
                                                     CASE
                                                        WHEN   EXTRACT (
                                                                  YEAR FROM (select sysdate from dual))
                                                             - EXTRACT (
                                                                  YEAR FROM g.DATE_OF_BIRTH) BETWEEN 60
                                                                                                 AND 69
                                                        THEN
                                                           '60-69'
                                                        ELSE
                                                           CASE
                                                              WHEN   EXTRACT (
                                                                        YEAR FROM (select sysdate from dual))
                                                                   - EXTRACT (
                                                                        YEAR FROM g.DATE_OF_BIRTH) >=
                                                                      70
                                                              THEN
                                                                 'ELDERLY'
                                                           END
                                                     END
                                               END
                                         END
                                   END
                             END
                       END
                 END
           END) from your table g

This is just an example. Replace ranges with your preferred ones. I did this using Oracle.