how to calculate age from Date of birth and how to group the records by age ranges-CodePudding

I have a table with a column 'DOB', I need to group the records by age ranges. but i don't know how to calculate age using DOB and use case along with it. I got that code from this site.

select 
  case
   when age_c <1 then 'Under 1,
   when age_c between 1 and 5 then '1-5',
   when age_c between 5 and 15 then '5-15',
   when age_c between 15 and 35 then '15-35'

What I want is:

 Under 1    1

  1-5       15 
     
  15-35     54

CodePudding user response：

You presumably want some kind of aggregation query here:

SELECT CASE WHEN age_c < 1  THEN 'Under 1'
            WHEN age_c < 5  THEN '1 - 5'
            WHEN age_c < 15 THEN '5 - 15'
            WHEN age_c < 35 THEN '15 - 35' END AS age_range,
       COUNT(*) AS cnt
FROM yourTable
GROUP BY 1;

CodePudding user response：

WITH AgeData as
(
  SELECT 
  cast(DATEDIFF(DAY,DOB,GETDATE())/365.25 as int)  AS [AGE]
  FROM your_table
),
GroupAge AS
(
  SELECT [Age],
         CASE
             WHEN AGE < 1 THEN 'Under 1',
             WHEN AGE BETWEEN 1 AND 5 THEN '1-5',
             WHEN AGE BETWEEN 5 AND 15 THEN '5-15',
             WHEN AGE BETWEEN 15 AND 35 THEN '15-35'
             WHEN AGE > 35 THEN 'Over 35'
         END AS [Age Groups]
  FROM AgeData
)
SELECT COUNT(*) AS [callGrpCount],
       [Age Groups]  FROM GroupAge 
GROUP BY [Age Groups]  order by [Age Groups]

Assume: DOB in yyyymmdd format