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 1 year ago

Hi Jacqui Hodgkinson,

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

 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 1 year ago in reply to Frédéric Rinaldi

Thanks Frédéric, a colleague and I are working on that now.
Cancel
Vote Up +1 Vote Down

Sign in to reply

Verify Answer

Cancel