Get user number group by age range and gender

[whisher]

Hi,
I'm again in the troubles

SELECT A.domain_id, U.birthday AS field, DATE( access_start_datetime ) AS DATE, COUNT( 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.birthday, A.domain_id 

with this query I get the number of the users group by birthday

SELECT A.domain_id, U.gender as field, DATE(access_start_datetime) as date, COUNT(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.gender, A.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

[r937]

please define how you want "age" to be calculated

is access_start_datetime the actual user's birthday???

[DaveMaxwell]

Something like this (not tested since I don't have MySQL installed anywhere....)

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;

[whisher]

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->

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

[whisher]

Something like this (not tested since I don't have MySQL installed anywhere....)

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))

[DaveMaxwell]

Ooops. Change all of the TO_DAYS(access_start_datetime) to TO_DAYS(U.Birthday)