SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Guru whisher's Avatar
    Join Date
    May 2006
    Location
    Kakiland
    Posts
    732
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Get user number group by age range and gender

    Hi,
    I'm again in the troubles
    PHP Code:
    SELECT A.domain_idU.birthday AS fieldDATEaccess_start_datetime ) AS DATECOUNTA.id ) AS value
    FROM stats_access A
    JOIN users U ON U
    .id A.fb_id
    GROUP BY DATE
    A.access_start_datetime ) , U.birthdayA.domain_id 
    with this query I get the number of the users group by birthday
    PHP Code:
    SELECT A.domain_idU.gender as fieldDATE(access_start_datetime) as dateCOUNT(A.id) as value
    FROM  stats_access A
    JOIN users U ON U
    .id A.fb_id
    GROUP BY DATE
    (A.access_start_datetime), U.genderA.domain_id
    with this query I get the number of the users group by gender

    but how is the query to get the number group by age range and gender
    like
    17-25 (male 10,female 5)
    25-35 (male 5,female 10)
    and so on

    A big thanks in advance

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    please define how you want "age" to be calculated

    is access_start_datetime the actual user's birthday???
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,200
    Mentioned
    105 Post(s)
    Tagged
    1 Thread(s)
    Something like this (not tested since I don't have MySQL installed anywhere....)

    Code:
    SELECT     A.domain_id
         ,     U.gender
         ,     CASE 
                WHEN FLOOR((TO_DAYS(NOW())- TO_DAYS(access_start_datetime)) / 365.25) < 17 THEN '1-16'
                WHEN FLOOR((TO_DAYS(NOW())- TO_DAYS(access_start_datetime)) / 365.25) > 16 < 25 THEN '17-24'
                WHEN FLOOR((TO_DAYS(NOW())- TO_DAYS(access_start_datetime)) / 365.25) > 24 < 36 THEN '25-35'
                :
                ELSE 'Older than Methesulah'
            END AS age
         ,     COUNT(A.id) AS AgeCount 
      FROM    stats_access A 
      INNER JOIN users U ON U.id = A.fb_id 
      GROUP BY A.domain_id
             , U.gender
             , CASE 
                    WHEN FLOOR((TO_DAYS(NOW())- TO_DAYS(access_start_datetime)) / 365.25) < 17 THEN '1-16'
                    WHEN FLOOR((TO_DAYS(NOW())- TO_DAYS(access_start_datetime)) / 365.25) > 16 < 25 THEN '17-24'
                    WHEN FLOOR((TO_DAYS(NOW())- TO_DAYS(access_start_datetime)) / 365.25) > 24 < 36 THEN '25-35'
                    :
                    ELSE 'Older than Methesulah'
               END;
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style

  4. #4
    SitePoint Guru whisher's Avatar
    Join Date
    May 2006
    Location
    Kakiland
    Posts
    732
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    please define how you want "age" to be calculated
    The age I want to be calculated
    13-17
    18-24
    25-34
    35-44
    45-54
    55-64
    65->

    Quote Originally Posted by r937 View Post
    is access_start_datetime the actual user's birthday???
    No, it's not but I need the data group by access_start_datetime (that's query is done to make a view querying by a range of time day,week,mont,year)

    Thanks for your help

  5. #5
    SitePoint Guru whisher's Avatar
    Join Date
    May 2006
    Location
    Kakiland
    Posts
    732
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by DaveMaxwell View Post
    Something like this (not tested since I don't have MySQL installed anywhere....)

    Code:
    SELECT     A.domain_id
         ,     U.gender
         ,     CASE 
                WHEN FLOOR((TO_DAYS(NOW())- TO_DAYS(access_start_datetime)) / 365.25) < 17 THEN '1-16'
                WHEN FLOOR((TO_DAYS(NOW())- TO_DAYS(access_start_datetime)) / 365.25) > 16 < 25 THEN '17-24'
                WHEN FLOOR((TO_DAYS(NOW())- TO_DAYS(access_start_datetime)) / 365.25) > 24 < 36 THEN '25-35'
                :
                ELSE 'Older than Methesulah'
            END AS age
         ,     COUNT(A.id) AS AgeCount 
      FROM    stats_access A 
      INNER JOIN users U ON U.id = A.fb_id 
      GROUP BY A.domain_id
             , U.gender
             , CASE 
                    WHEN FLOOR((TO_DAYS(NOW())- TO_DAYS(access_start_datetime)) / 365.25) < 17 THEN '1-16'
                    WHEN FLOOR((TO_DAYS(NOW())- TO_DAYS(access_start_datetime)) / 365.25) > 16 < 25 THEN '17-24'
                    WHEN FLOOR((TO_DAYS(NOW())- TO_DAYS(access_start_datetime)) / 365.25) > 24 < 36 THEN '25-35'
                    :
                    ELSE 'Older than Methesulah'
               END;
    it workish without the : in the CASE statement
    the result
    domain_id gender age AgeCount
    3 male 1*-16 3300
    3 female 1-*16 720
    4 male 1*-16 3214
    4 female 1*-16 756
    5 male 1*-16 3102
    5 female 1*-16 744

    but I've user older than 16 ^^
    all in all it's a good start point :P
    Thanks for your help.

    Btw
    out of curiosity
    Methesulah stand for what ?


    EDIT
    sorry I didn't see
    TO_DAYS(NOW())- TO_DAYS(access_start_datetime))
    it should be
    TO_DAYS(NOW())- TO_DAYS(U.birthday))

  6. #6
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,200
    Mentioned
    105 Post(s)
    Tagged
    1 Thread(s)
    Ooops. Change all of the TO_DAYS(access_start_datetime) to TO_DAYS(U.Birthday)
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •