How to count by current age given DOB and registration date sql

Question

I have a users table which includes: userID, userName, DOB, registrationDate. I would like to count members by their current age for members registered since 2000. I know to do the latter part of the query but struggle with the counting part:

SELECT COUNT (DOB) WHERE registration_date >= ‘01/01/2000’

Any help. Thanks

What RDBMSare you using? You ask "since 2000," but your quesry shows ‘01/01/2012’. — Karl Kieninger, Apr 06 '14 at 18:37
Can you please explain what you meant by "count members by their current age" ? It would be great if you could provide some example — heretolearn, Apr 06 '14 at 18:47

score 0 · Answer 1 · edited May 23 '17 at 12:11

You need to group member by ages so you need to calculate age in the GROUP BY clause. It complicated because you need to account for leap years. (I borrowed the leap year part from here.)

CREATE TABLE Users (userID INT, userName VARCHAR(10), DOB DATE, registrationDate DATE);

INSERT INTO Users VALUES 
 (1,'bob','1990-06-01','2005-06-01')
,(1,'old gus','1990-06-01','1995-06-01')
,(1,'joe','1991-01-01','2010-06-01')
,(1,'jim','1950-01-01','2010-06-01')


SELECT YEAR(CURDATE()) - YEAR(DOB) - (DATE_FORMAT(CURDATE(), '%m%d') < DATE_FORMAT(DOB, '%m%d'))
      ,COUNT(1)
  FROM Users
 WHERE registrationdate >= '01/01/2012'
 GROUP BY YEAR(CURDATE()) - YEAR(DOB) - (DATE_FORMAT(CURDATE(), '%m%d') < DATE_FORMAT(DOB, '%m%d'))

See a sqlfiddle.

How to count by current age given DOB and registration date sql

1 Answers1