sql query, select age range and show none information

[nitiphone2021]

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

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

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

[Barand]

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

 

[nitiphone2021]

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

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:

[Barand]

Try doing it as I did.

[nitiphone2021]

If I don't want to create real table, is there any way can create temp table?

[Barand]

CREATE TEMPORARY TABLE ( .... )