Yesterday, I was confronted with the need to count the number of users per age and gender in a table of registered users, in order to produce a nice bar chart. This can quite easily be achieved in (postgres)SQL as demonstrated below.
However, depending on your data this may result in quite too many rows. To reduce information overload, one may want to group the ages into intervals. However doing this manually in application code can be kind of tedious.
Thankfully on StackOverflow someone had answered a similar question and I adapted the response to this particular scenario by doing the following:
<LOWER_LIMIT> should be replaced with the lowest age occuring in the
<INTERVAL_SIZE> is the number of distinct ages to put into one
interval. This produces a nice result set where each row corresponds to an
age interval/gender pair. The
segment column then represents the (inclusive)
lower limit of each interval.
To make the query more automatic, we can pull the minimum age straight from the
table leaving the parameter
<INTERVAL_SIZE> as the only one that needs to
be explicity specified.