Jump to content

Mysql query, combine command about age and separate by gender


Recommended Posts

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

 

Link to post
Share on other sites

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.

Link to post
Share on other sites

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.

 

 

Link to post
Share on other sites
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 

Link to post
Share on other sites
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.

Link to post
Share on other sites

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

 

  • Like 1
Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.