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

Question

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?

Answer 1

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

Answer 2

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;