Search code examples
sqlcrystal-reports

How to show number of users by dynamic age groups

I have a table which store DoB of users. Now I need a report(crystal report) that shows how many users are there in ranges of age.

For example, the report need to shows:

Age: 1-10: 50 users

Age: 11-20: 30 users

Age: 21-30: 60 users And so on...

The report should follow rules below :

  1. The range of age can be changed easily (the range in example above is 10)

  2. The last range is calculated automatically based on the oldest user

Actually I don't have any idea How to do it for now. I really appreciate any help.

Thanks!

Solution

  • You can define the ranges using arithmetic. The exact syntax might vary by database, but it is basically:

    select (floor( (a - 1) / 10 ) * 10 + 1) || '-' ||  (floor( (a - 1) / 10 ) * 10 + 10) as age_range,
       count(*)
from t
group by (floor( (a - 1) / 10 ) * 10 + 1) || '-' ||  (floor( (a - 1) / 10 ) * 10 + 10)
order by min(age);

    The expression floor( (a - 1) / 10 ) * 10 + 1 is just a mathematical expression that gets the first year of the range -- so 1-10 are in one group, 11-20, and so on.