Sugar report - grouping age into categories

We use Sugar for keeping details of our scheme members, and need to report on their ages grouped into categories - we record date of birth but have been able to write in to generate their age from this. At present I've been exporting all their ages into Excel and running the calculations there, but this then prevents local schemes being able to self-serve if they need their local data. It looks like it might be possible through Advanced Reports but my SQL is very limited and I can't work it out - can anyone help please?

We need under 16s, then 16-24, 25-34 etc up to a final category of 95 and over.

0 Frédéric Rinaldi over 2 years ago
Hi Jacqui Hodgkinson,

you can try an advanced reports with a custom query that should looks like this

Fullscreen
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT birthdate, age,
CASE
WHEN age <16 then "16s"
WHEN age >=16 AND age <25 then "16-24"
WHEN age >=25 AND age <34 then "25-34"
WHEN age >=35 AND age <44 then "35-44"
WHEN age >=45 AND age <54 then "45-54"
/* keep on going */
ELSE "over 95"
END AS range_age
FROM (
SELECT c.birthdate,
DATE_FORMAT(FROM_DAYS(DATEDIFF(NOW(),c.birthdate)), '%Y') + 0 AS age
FROM contacts c
WHERE c.birthdate IS NOT NULL
) AS calculated_birth
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
SELECT birthdate, age, CASE WHEN age <16 then "16s" WHEN age >=16 AND age <25 then "16-24" WHEN age >=25 AND age <34 then "25-34" WHEN age >=35 AND age <44 then "35-44" WHEN age >=45 AND age <54 then "45-54" /* keep on going */ ELSE "over 95" END AS range_age FROM ( SELECT c.birthdate, DATE_FORMAT(FROM_DAYS(DATEDIFF(NOW(),c.birthdate)), '%Y') + 0 AS age FROM contacts c WHERE c.birthdate IS NOT NULL ) AS calculated_birth

I hope it helps.

Fred
0 Jacqui Hodgkinson over 2 years ago in reply to Frédéric Rinaldi

Thanks Frédéric, a colleague and I are working on that now.