Jump to content

sql query, select age range and show none information


Go to solution Solved by Barand,

Recommended Posts

Dear friend, I help about this command below

SELECT CASE WHEN `p_age` < 18 THEN 'under 18' WHEN `p_age` 
BETWEEN 18 and 29 THEN '18-29' WHEN `p_age` 
BETWEEN 30 and 39 THEN '30-39' WHEN `p_age` 
BETWEEN 40 and 49 THEN '40-49' WHEN `p_age` 
BETWEEN 50 and 59 THEN '50-59' WHEN `p_age` 
BETWEEN 60 and 69 THEN '60-69' WHEN `p_age` 
BETWEEN 70 and 79 THEN '70-79' WHEN `p_age` > 80 
THEN 'Over 80' END as RANGE_AGE, count(`id`) as COUNT FROM `tb_people_quarantine` 
GROUP BY RANGE_AGE ORDER BY RANGE_AGE";

and the result is below

image.png.61cd0906cc70cee3e35de38eb094d310.png

The result I want it will show 'OVER 80'      0

the same as other range age, if they are no information should show 0

Link to post
Share on other sites

They have to exist to be output.

Now if you'd done as I sugessted last time, and created an "age_group" table, the group would exist. And you are still excluding 80 year olds - do you read replies?

SELECT group_name
     , COUNT(i.id)
FROM age_group a 
     LEFT JOIN tb_infected i 
        ON i.age BETWEEN a.lo_age AND a.hi_age
GROUP BY age_group_id;

+------------+-------------+
| group_name | COUNT(i.id) |
+------------+-------------+
| Under 18   |         168 |
| 18 - 29    |         116 |
| 30 - 39    |          92 |
| 40 - 49    |         110 |
| 50 - 59    |         109 |
| 60 - 69    |         110 |
| 70 - 79    |         104 |
| 80+        |           0 |
+------------+-------------+

 

Link to post
Share on other sites

Yes, I tried but seem is not work on my laptop

image.png.7b3717501083ce457960cece903d040f.png

SELECT group_name as `Age Group`
     , COUNT(`id`) as Total
FROM `tb_people_quarantine` i 
     LEFT JOIN
     age_group a 
            ON i.`p_age` BETWEEN a.lo_age AND a.hi_age
GROUP BY age_group_id

Result below:

image.png.35293a0995231b81d6836c8ad6aef2fa.png

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.