tobeyt23 Posted March 22, 2011 Share Posted March 22, 2011 I am trying to get the average age based off the date of birth (mm/dd/yyyy) this is what I have but keep getting 51. Sure this is correct any suggestions? SELECT FLOOR(AVG(TO_DAYS(NOW())-TO_DAYS(CONCAT(SUBSTR(user_profiles.dob, 7,4),'-',SUBSTR(user_profiles.dob, 1, 2),'-',SUBSTR(user_profiles.dob, 4, 2))))/365.242199) FROM user_profiles WHERE user_profiles.dob !='' Quote Link to comment https://forums.phpfreaks.com/topic/231434-avg-of-date/ Share on other sites More sharing options...
fenway Posted March 30, 2011 Share Posted March 30, 2011 I have no idea what that means. Quote Link to comment https://forums.phpfreaks.com/topic/231434-avg-of-date/#findComment-1193967 Share on other sites More sharing options...
tobeyt23 Posted March 30, 2011 Author Share Posted March 30, 2011 I would like to get the avg age based off the dob each user enters which I have stored in the table as mm/dd/yy. I have the above query in place however it returns either 51 or 50. Quote Link to comment https://forums.phpfreaks.com/topic/231434-avg-of-date/#findComment-1193984 Share on other sites More sharing options...
fenway Posted April 3, 2011 Share Posted April 3, 2011 Either 51 or 50? What does that mean? Quote Link to comment https://forums.phpfreaks.com/topic/231434-avg-of-date/#findComment-1196246 Share on other sites More sharing options...
tobeyt23 Posted June 9, 2011 Author Share Posted June 9, 2011 It returns either avg age of 50 0r 51 .... this now is stuck on 60 buy have added a ton of new users and doesn't seem to be working correctly. Any help? Quote Link to comment https://forums.phpfreaks.com/topic/231434-avg-of-date/#findComment-1227415 Share on other sites More sharing options...
tobeyt23 Posted June 9, 2011 Author Share Posted June 9, 2011 What i have is the dob field in this format MM/DD/YYYY and need to get that avg age based on that for all users. Quote Link to comment https://forums.phpfreaks.com/topic/231434-avg-of-date/#findComment-1227419 Share on other sites More sharing options...
tobeyt23 Posted June 9, 2011 Author Share Posted June 9, 2011 Think this does it: SELECT ROUND((YEAR(NOW()) - AVG(STR_TO_DATE(user_profiles.dob,'%m/%d/%Y')))) FROM user_profiles WHERE user_profiles.dob != '' AND YEAR(STR_TO_DATE(user_profiles.dob,'%m/%d/%Y')) != YEAR(NOW()) AND YEAR(STR_TO_DATE(user_profiles.dob,'%m/%d/%Y')) != (YEAR(NOW())-1) Quote Link to comment https://forums.phpfreaks.com/topic/231434-avg-of-date/#findComment-1227448 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.