Group by age range mysql
I'm trying to count the number of people by age ranges, and I can almost do it with 2 problems:
Here's a sample of the db table "inquiries":
Here's the query:
Here's a simple solution based on the suggestion by Wrikken: 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 Two options. a) Use sub query
Gives
b) Group by age first and then group in range in application. When using ORMs it's not easy to do sub queries, so this might be a good idea. Performance wise: Considering that average humans live for 100 years, there can be at max 200 records (100 male and 100 female) which can be easily looped in an application.
Gives |