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

1)MySQL or SQL SERVER? 2)Do you really mean to group those people (to count them for example)? Or do you want to just add a column saying what group they're in? — Jakub Kania, Apr 04 '14 at 12:43
Should you trust a dbms that lets you cast a date as an integer? — Mike Sherrill 'Cat Recall', Apr 04 '14 at 12:56
@JakubKania i am using MySQL but I just wanted to get the idea behind it really not really the syntax. And, yes I would like to count them and obviously display them in their different groupings afterwards. Thanks — saint, Apr 04 '14 at 12:56

score 6 · Accepted Answer · answered Apr 04 '14 at 13:00

6

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.

answered Apr 04 '14 at 13:00

Bohemian

412,405
93
575
722

1

Excellent! Just note that if the value is null, it would fall in the `else` clause. Add `WHEN date_of_birth IS NULL then 'NULL'` to make it more specific. – RominaV Jan 30 '16 at 16:40

Dmitry Bychenko · Answer 2 · 2014-04-04T12:50:01.577

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

edited Apr 04 '14 at 12:50

answered Apr 04 '14 at 12:41

Dmitry Bychenko

180,369
20
160
215

The question is a bit confusing but it does specifically mention age **range**. – Jakub Kania Apr 04 '14 at 12:47
If range (not just age) is required one can use appropriate case (see my edit) – Dmitry Bychenko Apr 04 '14 at 12:51
I know what one can use, I just wanted to let you know you should read more carefully :) – Jakub Kania Apr 04 '14 at 13:12
I did mean the age range but obviously wanted to get the age first to know what range it fits into. I have voted up your answer but I think Bohemain's answer above is a bit more intuitive. – saint Apr 04 '14 at 13:18

Stanley Mungai · Answer 3 · 2014-04-04T13:09:34.777

I was once faced with the same requirement and this is How I fixed it: I wish there was a Straight Forward way as 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 example. Replace the ranges with your Preferred Ones. This I have done using Oracle.

Although I think this would work fine, I think its a bit long and there is a shorter way of solving the query. take a look at Bohemian's solution below.. — saint, Apr 04 '14 at 13:13
I said it :) It is not very straight forward, but it serves the purpose. — Stanley Mungai, Apr 04 '14 at 13:21

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

3 Answers3