CODEWITHSUNDEEP
sqlsql-serverdatabase
Lee
Lee

Reputation: 3969

Return number of people of a certain age deduced from stored DOBs

I'm using the following query to return the ages (in years) of all stored user date of births. It would be useful if I could essentially tally the ages so I could generate a report. The query is chunky and therefore makes it very difficult for me to understand how I'd do this, especially since aggregate functions cannot be assigned aliases.

Select
    DateDiff(YEAR, dob, CURRENT_TIMESTAMP) - 
    CASE WHEN
        DATEADD(year,DateDiff(YEAR, dob, CURRENT_TIMESTAMP),CURRENT_TIMESTAMP)
        > CURRENT_TIMESTAMP
    THEN 1
    ELSE 0 END as ageYears

from [database].[dbo].[users]

Using the distinct keyword I can return the age groups, not the instances, but how would I join these with a count?

This output would be ideal:

age | numUsers
18  | 1
19  | 3
20  | 7
21  | 1
22  | 9
23  | 2

Upvotes: 0

Views: 64

Answers (3)

M.Ali
M.Ali

Reputation: 69524

Test Data

DECLARE @T TABLE (ID INT, DOB DATE)
INSERT INTO @T 
VALUES (1, '1940-01-01'),(2, '1940-01-01'),(3, '1985-01-01')
,(4, '1985-01-01'),(1, '1999-01-01'),(1, '1968-01-01')

Query

SELECT     DateDiff(YEAR, dob, CURRENT_TIMESTAMP) - 
    CASE WHEN
        DATEADD(year,DateDiff(YEAR, dob, CURRENT_TIMESTAMP),CURRENT_TIMESTAMP)
        > CURRENT_TIMESTAMP
    THEN 1
    ELSE 0 END  AS Age , COUNT(*) AS Total
FROM @T
GROUP BY DateDiff(YEAR, dob, CURRENT_TIMESTAMP) - 
    CASE WHEN
        DATEADD(year,DateDiff(YEAR, dob, CURRENT_TIMESTAMP),CURRENT_TIMESTAMP)
        > CURRENT_TIMESTAMP
    THEN 1
    ELSE 0 END

Result

╔═════╦═══════╗
║ Age ║ Total ║
╠═════╬═══════╣
║  14 ║     1 ║
║  28 ║     2 ║
║  45 ║     1 ║
║  73 ║     2 ║
╚═════╩═══════╝

Upvotes: 1

hashbrown
hashbrown

Reputation: 3516

Can you try this,

Select X.age, sum (X.num) numUsers
From (
Select
    DateDiff(YEAR, dob, CURRENT_TIMESTAMP) - 
    CASE WHEN
        DATEADD(year,DateDiff(YEAR, dob, CURRENT_TIMESTAMP),CURRENT_TIMESTAMP)
        > CURRENT_TIMESTAMP
    THEN 1
    ELSE 0 END age, 
    1 num

from [database].[dbo].[users]
) X
group by X.age

Upvotes: 2

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239694

Just use a CTE or subquery to create the ages first, and then a simple group by query to tally them up:

; With Ages as (
    Select
        DateDiff(YEAR, dob, CURRENT_TIMESTAMP) - 
        CASE WHEN
            DATEADD(year,DateDiff(YEAR, dob, CURRENT_TIMESTAMP),CURRENT_TIMESTAMP)
            > CURRENT_TIMESTAMP
        THEN 1
        ELSE 0 END as ageYears

    from [database].[dbo].[users]
)
SELECT ageYears,COUNT(*) as NumUsers
FROM Ages
GROUP BY ageYears

The alternative, that doesn't need the subquery or CTE is to repeat the entire complex expression in the GROUP BY clause:

Select
    DateDiff(YEAR, dob, CURRENT_TIMESTAMP) - 
    CASE WHEN
        DATEADD(year,DateDiff(YEAR, dob, CURRENT_TIMESTAMP),CURRENT_TIMESTAMP)
        > CURRENT_TIMESTAMP
    THEN 1
    ELSE 0 END as ageYears,
    COUNT(*) as numUsers
from [database].[dbo].[users]
group by
DateDiff(YEAR, dob, CURRENT_TIMESTAMP) - 
    CASE WHEN
        DATEADD(year,DateDiff(YEAR, dob, CURRENT_TIMESTAMP),CURRENT_TIMESTAMP)
        > CURRENT_TIMESTAMP
    THEN 1
    ELSE 0 END as ageYears

Personally, I prefer the former version.

Upvotes: 2

Related Questions