nitiphone2021 0 Posted January 31 Share Posted January 31 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 Quote Link to post Share on other sites
Barand 1,649 Posted January 31 Share Posted January 31 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 | +------------+-------------+ Quote Link to post Share on other sites
nitiphone2021 0 Posted February 1 Author Share Posted February 1 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: Quote Link to post Share on other sites
Solution Barand 1,649 Posted February 2 Solution Share Posted February 2 Try doing it as I did. Quote Link to post Share on other sites
nitiphone2021 0 Posted February 3 Author Share Posted February 3 If I don't want to create real table, is there any way can create temp table? Quote Link to post Share on other sites
Barand 1,649 Posted February 3 Share Posted February 3 CREATE TEMPORARY TABLE ( .... ) 1 Quote Link to post Share on other sites
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.