Question 21774 in Mysql Question posted in
The official MySQL documentation can be found here.

I want to group by age filter by in between age and show 0 count for data not found in between – Mysql

I am facing issue in showing the data for age group by in between. Here is my query:

SELECT 
    CASE WHEN age BETWEEN 0 AND 18 OR age IS NULL THEN '0-18' 
         WHEN age BETWEEN 19 AND 30 THEN '19-30' 
         WHEN age BETWEEN 31 AND 35 THEN '31-35' 
         WHEN age BETWEEN 36 AND 50 THEN '36-50' 
        WHEN age BETWEEN 51 AND 100 THEN '50+' 
    END AS age_group, 
    COALESCE(COUNT(*), 0) AS count 
FROM patient_registration 
GROUP BY age_group;

I am trying to run the above query to show data as you can see the above query executes without any error but I need a solution for age group where the count is 0 for eg.

I don’t have record for age in between 0 to 18 it’s not showing in the output, but I want to show the record like this

age_group count
0-18 0
19-30 192
31-35 83
36-50 223
50+ 222

I want to show count 0 if the case doesn’t satisfy.

Here’s what I’ve tried so far:

SELECT 
  CASE 
    WHEN age BETWEEN 0 AND 18 OR age COUNT is NULL THEN '0-18'
    WHEN age BETWEEN 19 AND 30 THEN '19-30'
    WHEN age BETWEEN 31 AND 35 THEN '31-35'
    WHEN age BETWEEN 36 AND 50 THEN '36-50'
    WHEN age BETWEEN 51 AND 100 THEN '50+'
  END AS age_group, 
  COALESCE(COUNT(*), NULL) AS count
FROM patient_registration
GROUP BY age_group;

Is there any alternate method which I can try?

enter image description here

Tags:

3

Answers


  1. 0

    To achieve your desired result you have to use a dummy table and then use LEFT join to generate this result –

    SELECT age_group,
       coalesce(count(*),0) as cnt
  FROM (SELECT '0-18' age_grp
         UNION ALL
        SELECT '19-30'
         UNION ALL
        SELECT '31-35'
         UNION ALL
        SELECT '36-50'
         UNION ALL
        SELECT '50+'
      ) all_grp
  LEFT JOIN (SELECT CASE WHEN age BETWEEN 0 AND 18 OR age IS NULL THEN '0-18' 
                         WHEN age BETWEEN 19 AND 30 THEN '19-30' 
                         WHEN age BETWEEN 31 AND 35 THEN '31-35' 
                         WHEN age BETWEEN 36 AND 50 THEN '36-50' 
                         WHEN age BETWEEN 51 AND 100 THEN '50+' 
                    END AS age_group, 
                    COUNT(*) AS count 
               FROM patient_registration 
              GROUP BY age_group
              ) d ON all_grp.age_grp = d.age_group;
    Login or Signup to reply.
  2. 0 
    SELECT age_groups.age_group,
       COUNT(*) `count`
FROM ( 
    SELECT 0 minimal, 18 maximal, '0-18' age_group
    UNION ALL
    SELECT 19, 30, '19-30'
    UNION ALL
    SELECT 31, 35, '31-35'
    UNION ALL
    SELECT 36, 50, '36-50'
    UNION ALL
    SELECT 51, 999, '50+'
    ) age_groups
LEFT JOIN patient_registration 
    ON patient_registration.age BETWEEN age_groups.minimal AND age_groups.maximal
GROUP BY 1;

    The query will not count the rows where the age is invalid (negative, above 999) or is not set (is NULL).

    Additionally you may have a table which contains a lot of ranges sets, and use it instead of synthetic table making the query dynamic.

    Login or Signup to reply.
  3. 0

    This option might work for you. I returns all columns regardless of a count and each column IS the age-bracket in question.

    SELECT 
      sum( case WHEN age BETWEEN 0 AND 18 
                OR age IS NULL THEN 1 else 0 end ) Age0_18, 
      sum( case WHEN age BETWEEN 19 AND 30 THEN 1 else 0 end ) Age19_30, 
      sum( case WHEN age BETWEEN 31 AND 35 THEN 1 else 0 end ) Age31_35, 
      sum( case WHEN age BETWEEN 36 AND 50 THEN 1 else 0 end ) Age36_50, 
      sum( case WHEN age > 50 THEN 1 else 0 end ) AgeOver50 
   FROM 
      patient_registration

    Result would be

    Age0_18   Age19_30   Age31_35   Age36_50   AgeOver50
0         192        83         223        222
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search