Hello everyone this is the first time i am posting my question please wish me luck, 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

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?

CodePudding user response：

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;

CodePudding user response：

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).

CodePudding user response：

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