Details about this thread
  • State Not Answered
  • Replies 2 replies
  • Subscribers 310 subscribers
  • Views 558 views
  • Users 0 members are here
Related content from around SugarClub

Sugar report - grouping age into categories

Jacqui Hodgkinson

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.

Parents
  • 0
    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
Reply
  • 0
    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
Children