XF 1.2 Query to find out average age of users?

[tajhay]

Is there a query to find out the average age of your users (who have entered in their bday)?

 

[Rigel Kentaurus]

Yes, you can use this one

SELECT
    AVG(year(now()) - dob_year)
FROM xf_user_profile
WHERE
    dob_year > 0 AND
    (year(now()) - dob_year) > 0;

 

[Rigel Kentaurus]

And this one will give you the summary of
YEAR - NUMBER OF USERS

SELECT
    (year(now()) - dob_year) AS age,
    count(*)
FROM xf_user_profile
WHERE
    dob_year > 0 AND
    (year(now()) - dob_year) > 0
GROUP BY
    (year(now()) - dob_year)
ORDER BY age

 

[Rigel Kentaurus]

If you want to get really technical. As in "that person has 25.7 years, not 26, and you are counting it as 26), you can use this query.
It is more precise

SELECT 
    AVG(DATEDIFF(NOW(), STR_TO_DATE(CONCAT(dob_day, ',' ,dob_month, ',' ,dob_year),'%d,%m,%Y')) / 365)
FROM
    xf_user_profile
WHERE
    dob_year > 0;

 

[tajhay]

Thanks so much. Champion!