3

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.

2
  • Search for pivot mysql.
    – Mihai
    Sep 9 '14 at 12:57
  • 1
    This answer as well could also be of help. Sep 9 '14 at 13:38
4

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 |
+--------+------+----------+
4
  • Thank you @bibstha, but I wanted both the genders: male and female in the same column. Your technique gives same age range twice for male and female patients. Any way out please?
    – sangam
    Sep 10 '14 at 11:21
  • @sangam Not sure but could be done in application level instead of database. Sep 10 '14 at 11:22
  • Thank you so much @bibstha, I finally queried how you described and for the rest, I am playing with application code now. :)
    – sangam
    Sep 10 '14 at 12:35
  • Note that this solution may show that it counts twice those that have an age in the boundaries, ie. if someone has 10 years, it would count in the 0-10 and 10-20 group.
    – Braiam
    May 22 '18 at 17:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.