如何从出生日期计算年龄以及如何按年龄范围对记录进行分组

Question

我有一个列'DOB'表，我需要按年龄范围对记录进行分组。但我不知道如何使用DOB和用例来计算年龄。我从这个网站上得到的代码。

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'

我要的是:

Under 1    1
1-5       15 

15-35     54

Answer 1

这里可能需要某种聚合查询:

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;

Answer 2

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]

假设:DOB格式为yyyymmdd