Mysql query, combine command about age and separate by gender

[nitiphone2021]

Dear friends,

I have a problem about my website and my website they have a lot of query information. so it make my website very slow.

I would like to make query command is shorter and faster as these 3 commands.

COMMAND1: show all people

COMMAND2: show only male

COMMAND3: show only female.

Any way I can combine them? Is it faster than old command?

COMMAND 1
SELECT CASE WHEN `if_age` < 18 THEN 'under 18' 
WHEN `if_age` BETWEEN 18 and 29 THEN '18-29' WHEN `if_age` BETWEEN 30 and 39 THEN '30-39' 
WHEN `if_age` BETWEEN 40 and 49 THEN '40-49' WHEN `if_age` BETWEEN 50 and 59 THEN '50-59' 
WHEN `if_age` BETWEEN 60 and 69 THEN '60-69' WHEN `if_age` BETWEEN 70 and 79 THEN '70-79' 
WHEN `if_age` > 80 THEN 'Over 80' END as RANGE_AGE, count(`people_id`) as COUNT
FROM `tb_infected` WHERE tb_infected.qf_id = 1 GROUP BY RANGE_AGE ORDER BY RANGE_AGE

COMMAND2
SELECT CASE WHEN `if_age` < 18 THEN 'under 18' 
WHEN `if_age` BETWEEN 18 and 29 THEN '18-29' WHEN `if_age` BETWEEN 30 and 39 THEN '30-39' 
WHEN `if_age` BETWEEN 40 and 49 THEN '40-49' WHEN `if_age` BETWEEN 50 and 59 THEN '50-59' 
WHEN `if_age` BETWEEN 60 and 69 THEN '60-69' WHEN `if_age` BETWEEN 70 and 79 THEN '70-79' 
WHEN `if_age` > 80 THEN 'Over 80' END as RANGE_AGE, count(`people_id`) as COUNT
FROM `tb_infected` WHERE tb_infected.qf_id = 1 and if_gender = 0 GROUP BY RANGE_AGE ORDER BY RANGE_AGE

COMMAND3
SELECT CASE WHEN `if_age` < 18 THEN 'under 18' 
WHEN `if_age` BETWEEN 18 and 29 THEN '18-29' WHEN `if_age` BETWEEN 30 and 39 THEN '30-39' 
WHEN `if_age` BETWEEN 40 and 49 THEN '40-49' WHEN `if_age` BETWEEN 50 and 59 THEN '50-59' 
WHEN `if_age` BETWEEN 60 and 69 THEN '60-69'WHEN `if_age` BETWEEN 70 and 79 THEN '70-79' 
WHEN `if_age` > 80 THEN 'Over 80' END as RANGE_AGE, count(`people_id`) as COUNT
FROM `tb_infected` WHERE qf_id = 1 and if_gender = 1 GROUP BY RANGE_AGE ORDER BY RANGE_AGE

 

[requinix]

Add the if_gender to the list of SELECTed fields as well as the GROUP BY. Then you'll get one set of results that shows the count per age range and gender.

[Barand]

I'd be tempted to have an age_group table (id, group_name, lo_age, hi_age) and join to that on if_age BEWEEN lo_age AND hi_age. It would save all those case statements and ensure consistency.

BTW, your current age ranges exclude 80 year olds.

 

 

[nitiphone2021]

19 hours ago, requinix said:

Add the if_gender to the list of SELECTed fields as well as the GROUP BY. Then you'll get one set of results that shows the count per age range and gender.

This one can be work but not include the COMMAND3, it's total gender 

[requinix]

1 minute ago, nitiphone2021 said:

This one can be work but not include the COMMAND3, it's total gender 

...which is a problem that can be solved with a very simple application of math.

[Barand]

Using an "age_group" table as suggested and a test table with 999 random records ...

+--------------+------------+--------+--------+       +--------+---------------+------+-----+---------+----------------+
| age_group_id | group_name | lo_age | hi_age |       | Field  | Type          | Null | Key | Default | Extra          |
+--------------+------------+--------+--------+       +--------+---------------+------+-----+---------+----------------+
|            1 | Under 18   |      0 |     17 |       | id     | int(11)       | NO   | PRI | NULL    | auto_increment |
|            2 | 18 - 29    |     18 |     29 |       | name   | varchar(30)   | YES  |     | NULL    |                |
|            3 | 30 - 39    |     30 |     39 |       | age    | tinyint(4)    | YES  |     | NULL    |                |
|            4 | 40 - 49    |     40 |     49 |       | gender | enum('M','F') | YES  |     | NULL    |                |
|            5 | 50 - 59    |     50 |     59 |       +--------+---------------+------+-----+---------+----------------+
|            6 | 60 - 69    |     60 |     69 |
|            7 | 70 - 79    |     70 |     79 |
|            8 | 80+        |     80 |    120 |
+--------------+------------+--------+--------+

... then ...

SELECT group_name as `Age Group`
     , SUM(gender='M') as Male
     , SUM(gender='F') as Female
     , COUNT(*) as Total
FROM tb_infected i 
     JOIN
     age_group a 
            ON i.age BETWEEN a.lo_age AND a.hi_age
GROUP BY age_group_id;

giving

+------------+------+--------+-------+
| Age Group  | Male | Female | Total |
+------------+------+--------+-------+
| Under 18   |   70 |     98 |   168 |
| 18 - 29    |   44 |     72 |   116 |
| 30 - 39    |   42 |     50 |    92 |
| 40 - 49    |   54 |     56 |   110 |
| 50 - 59    |   47 |     62 |   109 |
| 60 - 69    |   42 |     68 |   110 |
| 70 - 79    |   36 |     68 |   104 |
| 80+        |   76 |    114 |   190 |
+------------+------+--------+-------+