Group by age range and gender with aggregation function on some columns

In Mysql, I have a patient table with id, birth_year and gender. Next table is transactions in which I have id, patient_id [FK from patient] and checkup_date. I need to select rows such that I get number of checkups for all males and females in the range of ages.

How do I query to achieve the following tabular result ?

under 20, male: 10 times, female: 15 times

21 - 30, male: 20 times, female: 35 times

31 - 40, male: 30 times, female: 45 times

.... ....... ......... ....... ........ ..........

81 - 90, male: 10 times, female: 6 times

above 90, male : 0 times, female: 4 times

Thank you.

Two options.

a) Use sub query

select 
  concat(10*floor(age/10), '-', 10*floor(age/10) + 10) as `range`, 
  gender, 
  count(*) as count 
from (
  select 
    *, 
    TIMESTAMPDIFF(YEAR,birth_year,CURDATE()) AS age
  from 
    transaction left join patient on patient_id = patient.id
) as t group by `range`, gender;

Gives

+-------+--------+-------+
| range | gender | count |
+-------+--------+-------+
| 0-10  | m      |     2 |
| 10-20 | m      |     1 |
| 20-30 | m      |     3 |
| 30-40 | f      |     2 |
| 30-40 | m      |     2 |
| 50-60 | f      |     1 |
| 50-60 | m      |     1 |
| 60-70 | f      |     5 |
| 60-70 | m      |     3 |
| 70-80 | f      |     1 |
| 70-80 | m      |     6 |
+-------+--------+-------+

b) Group by age first and then group in range in application.

When using ORMs it's not easy to do sub queries, so this might be a good idea.

Performance wise: Considering that average humans live for 100 years, there can be at max 200 records (100 male and 100 female) which can be easily looped in an application.

select 
  gender, 
  TIMESTAMPDIFF(YEAR,birth_year,CURDATE()) AS age,
  count(*)
from 
  transaction left join patient on patient_id = patient.id
group by age, gender

Gives

+--------+------+----------+
| gender | age  | count(*) |
+--------+------+----------+
| m      |    4 |        2 |
| m      |   14 |        1 |
| m      |   24 |        3 |
| f      |   34 |        2 |
| m      |   34 |        2 |
| f      |   54 |        1 |
| m      |   54 |        1 |
| f      |   64 |        5 |
| m      |   64 |        3 |
| f      |   74 |        1 |
| m      |   74 |        6 |
+--------+------+----------+

StackExchange Database Administrators Q#76078, answer score: 4