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