1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

XF 1.2 Query to find out average age of users?

Discussion in 'XenForo Questions and Support' started by tajhay, Sep 27, 2013.

  1. tajhay

    tajhay Well-Known Member

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

    Rigel Kentaurus Well-Known Member

    Yes, you can use this one
    Code:
    SELECT
        AVG(year(now()) - dob_year)
    FROM xf_user_profile
    WHERE
        dob_year > 0 AND
        (year(now()) - dob_year) > 0;
    
     
    tajhay likes this.
  3. Rigel Kentaurus

    Rigel Kentaurus Well-Known Member

    And this one will give you the summary of
    YEAR - NUMBER OF USERS
    Code:
    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
    
     
    tajhay likes this.
  4. Rigel Kentaurus

    Rigel Kentaurus Well-Known Member

    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

    Code:
    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 likes this.
  5. tajhay

    tajhay Well-Known Member

    Thanks so much. Champion!
     

Share This Page