CODEWITHSUNDEEP
sqlcount
nk10
nk10

Reputation: 23

Count Based on Age SQL

I have the following T-SQL to calculate ages:

SELECT
  Member_ID,
  DATEDIFF(YY,DOB,GETDATE()) - 
  CASE 
    WHEN DATEADD(YY,DATEDIFF(YY,DOB,GETDATE()),DOB) > GETDATE() THEN 1
    ELSE 0
  END AS Age in Years
FROM MEMBER
WHERE YEAR(registration_date ) >= 2012

How do I count the number of member IDs for each age in years?

Upvotes: 2

Views: 106

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

I would do this using a subquery or CTE. Much easier to follow:

SELECT AgeInYears, COUNT(*)
FROM (SELECT Member_ID,
             (DATEDIFF(YY,DOB,GETDATE()) - 
              CASE WHEN DATEADD(YY,DATEDIFF(YY,DOB,GETDATE()),DOB) > GETDATE() THEN 1
                   ELSE 0
              END) AS AgeinYears
      FROM MEMBER
      WHERE YEAR(registration_date ) >= 2012
     ) m
GROUP BY AgeInYears
ORDER BY 1;

Upvotes: 1

Dan Bracuk
Dan Bracuk

Reputation: 20794

Something like this:

SELECT
-- Member_ID,  commented out
DATEDIFF(YY,DOB,GETDATE()) - 
CASE 
WHEN DATEADD(YY,DATEDIFF(YY,DOB,GETDATE()),DOB) > GETDATE() THEN 1
ELSE 0
END AS Age in Years
, count(member_id) membersWithThisAge
FROM MEMBER
WHERE YEAR(registration_date ) >= 2012
group by DATEDIFF(YY,DOB,GETDATE()) - 
CASE 
WHEN DATEADD(YY,DATEDIFF(YY,DOB,GETDATE()),DOB) > GETDATE() THEN 1
ELSE 0
END

The member_id can't be included in the select clause. If it were, then you would get a count of one age for each member.

Upvotes: 0

Related Questions