MySQL – Group by Age Range and Gender with Aggregation

group byMySQLmysql-5.5select

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.

Best Answer

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 |
+--------+------+----------+

Related Solutions

MySQL Performance – How to Optimize Slow Query with Joins and GROUP BY

OK, I ended up adding another lookup table:

CREATE TABLE IF NOT EXISTS `stops_routes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `stop_id` varchar(100) NOT NULL,
  `route_id` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `stop_route` (`stop_id`,`route_id`),
  KEY `stop_id` (`stop_id`),
  KEY `route_id` (`route_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

Filling it was fairly fast:

mysql> insert into stops_routes (stop_id, route_id)
    ->
    -> select
    ->     s.stop_id,
    ->     r.route_id as from_route_id
    ->
    -> from routes r
    -> left join trips t on t.route_id = r.route_id
    -> left join stop_times st on st.trip_id = t.trip_id
    -> left join stops s on s.stop_id = st.stop_id
    -> group by s.stop_id, r.route_id;
Query OK, 3496 rows affected (8.38 sec)
Records: 3496  Duplicates: 0  Warnings: 0

Using it is blazingly fast:

mysql> select
    ->     r.route_id as from_route_id,
    ->     c_sr.route_id as to_route_id
    ->
    -> from routes r
    ->
    -> left join stops_routes sr on sr.route_id = r.route_id
    -> left join stop_connections c_s on c_s.from_stop_id = sr.stop_id
    -> left join stops_routes c_sr on c_sr.stop_id = c_s.to_stop_id
    ->
    -> where r.route_id <> c_sr.route_id
    -> group by r.route_id, c_sr.route_id
    -> limit 10;
+---------------+-------------+
| from_route_id | to_route_id |
+---------------+-------------+
| 0001          | 0002        |
| 0001          | 0003        |
| 0001          | 0004        |
| 0001          | 0005        |
| 0001          | 0006        |
| 0001          | 0008        |
| 0001          | 0009        |
| 0001          | 0011        |
| 0001          | 0014        |
| 0001          | 0031        |
+---------------+-------------+
10 rows in set (0.63 sec)

Now I can fill my last lookup table (set of connections between every routes on my GTFS network):

mysql> insert into route_connections (from_route_id, to_route_id)
    -> select
    ->     r.route_id as from_route_id,
    ->     c_sr.route_id as to_route_id
    ->
    -> from routes r
    ->
    -> left join stops_routes sr on sr.route_id = r.route_id
    -> left join stop_connections c_s on c_s.from_stop_id = sr.stop_id
    -> left join stops_routes c_sr on c_sr.stop_id = c_s.to_stop_id
    ->
    -> where r.route_id <> c_sr.route_id
    -> group by r.route_id, c_sr.route_id;
Query OK, 2848 rows affected (0.31 sec)
Records: 2848  Duplicates: 0  Warnings: 0

Amazingly fast. I guess the engine couldn't break up the steps to optimize this.
I'd still be interested to know if it would be possible to get the same result (from route to route connections table) using only one sub-second or sub-minute query.

MySQL 5.5 – Find Total Duration of Each Consecutive Series of Rows

Query 1, tested in SQLFiddle-1

SET @ward_id_to_check = 1 ;

SELECT
    st.patient_id,
    st.bed_id AS starting_bed_id,          -- the first bed a patient uses
                                           -- can be omitted
    st.admitted,
    MIN(en.discharged) AS discharged
FROM
  ( SELECT patient_id, bed_id, admitted, discharged
    FROM t 
    WHERE t.ward_id = @ward_id_to_check
      AND NOT EXISTS
          ( SELECT * 
            FROM t AS prev 
            WHERE prev.ward_id = @ward_id_to_check
              AND prev.patient_id = t.patient_id
              AND prev.discharged = t.admitted
          )
  ) AS st
JOIN
  ( SELECT patient_id, admitted, discharged
    FROM t 
    WHERE t.ward_id = @ward_id_to_check
      AND NOT EXISTS
          ( SELECT * 
            FROM t AS next 
            WHERE next.ward_id = @ward_id_to_check
              AND next.patient_id = t.patient_id
              AND next.admitted = t.discharged
          )
  ) AS en
    ON  st.patient_id = en.patient_id
    AND st.admitted <= en.admitted
GROUP BY
    st.patient_id,
    st.admitted ;

Query 2, which is the same as 1 but without the derived tables. This will probably have better execution plan, with proper indexes. Test in SQLFiddle-2:

SET @ward_id_to_check = 1 ;

SELECT
    st.patient_id,
    st.bed_id AS starting_bed_id,
    st.admitted,
    MIN(en.discharged) AS discharged
FROM
    t AS st    -- starting period
  JOIN
    t AS en    -- ending period
      ON  en.ward_id = @ward_id_to_check
      AND st.patient_id = en.patient_id
      AND NOT EXISTS
          ( SELECT * 
            FROM t AS next 
            WHERE next.ward_id = @ward_id_to_check
              AND next.patient_id = en.patient_id
              AND next.admitted = en.discharged
          )
      AND st.admitted <= en.admitted
WHERE 
      st.ward_id = @ward_id_to_check
  AND NOT EXISTS
      ( SELECT * 
        FROM t AS prev 
        WHERE prev.ward_id = @ward_id_to_check
          AND prev.patient_id = st.patient_id
          AND prev.discharged = st.admitted
      )
GROUP BY
    st.patient_id,
    st.admitted ;

Both queries assume that there is a unique constraint on (patient_id, admitted). If the server runs with strict ANSI settings, the bed_id should be added in the GROUP BY list.

Best Answer

Related Solutions

MySQL Performance – How to Optimize Slow Query with Joins and GROUP BY

MySQL 5.5 – Find Total Duration of Each Consecutive Series of Rows

Related Question