select 
    code,
    max(age) age_level,
    max(age_interval) age_interval,
    sum(total) total
from 
    (select 
        (case  
            when floor(months_between(sysdate, u.birth_date)/12) between 13 and 18 then 'Өсвөр үе' 
            when floor(months_between(sysdate, u.birth_date)/12) between 19 and 25 then 'Залуу үе'
            when floor(months_between(sysdate, u.birth_date)/12) between 26 and 35 then 'Идэр үе'
            when floor(months_between(sysdate, u.birth_date)/12) between 36 and 45 then 'Хижээл үе'
            when floor(months_between(sysdate, u.birth_date)/12) between 46 and 60 then 'Өтөл үе'            
            when floor(months_between(sysdate, u.birth_date)/12) between 60 and 100 then 'Өндөр үе'
            else 'other'
        end) age,
        (case  
            when floor(months_between(sysdate, u.birth_date)/12) between 13 and 18 then 'kid'
            when floor(months_between(sysdate, u.birth_date)/12) between 19 and 25 then 'bigger_kid'
            when floor(months_between(sysdate, u.birth_date)/12) between 26 and 35 then 'adult'
            when floor(months_between(sysdate, u.birth_date)/12) between 36 and 45 then 'bigger_adult'
            when floor(months_between(sysdate, u.birth_date)/12) between 46 and 60 then 'big_adult'            
            when floor(months_between(sysdate, u.birth_date)/12) between 60 and 100 then 'caption'
            else 'other'
        end) code,
        (case  
            when floor(months_between(sysdate, u.birth_date)/12) between 13 and 18 then '13-18'
            when floor(months_between(sysdate, u.birth_date)/12) between 19 and 25 then '19-25'
            when floor(months_between(sysdate, u.birth_date)/12) between 26 and 35 then '26-35'
            when floor(months_between(sysdate, u.birth_date)/12) between 36 and 45 then '36-45'
            when floor(months_between(sysdate, u.birth_date)/12) between 46 and 60 then '46-60'            
            when floor(months_between(sysdate, u.birth_date)/12) between 60 and 100 then '60-100'
            else 'other'
        end) age_interval,
        count(u.id) total
    from sec_survey_users su 
        left join sec_users u on su.user_id = u.id 
    where su.survey_id = 'D3A21B1C2D8334C9E055824F7FFC5DF4' group by u.birth_date) mtable group by mtable.code;

CodePudding user response：

Something like this could do it with less repetition (or define the age ranges in a permanent table and skip the with clause):

with age_classes (min_age, max_age, age_interval, code, age) as
      ( select 13, 18,  '13-18',  'teen',                   'Teenager'       from dual union all
        select 19, 25,  '19-25',  'young',                  'Young adult'    from dual union all
        select 26, 35,  '26-35',  'still_young',            'It won''t last' from dual union all
        select 36, 45,  '36-45',  'slightly_less_young',    'Middle aged'    from dual union all
        select 46, 50,  '46-60',  'still_got_it',           'Still got it'   from dual union all    
        select 60, 100, '60-100', 'exceeding_epectations',  'Senior'         from dual
      )
select nvl(ac.code,'other') as code
     , nvl(ac.age,'other')  as age
     , nvl(ac.age_interval,'other') as age_interval
     , count(*) total
from   sec_survey_users su 
       left join sec_users u on su.user_id = u.id 
       left join age_classes ac on floor(months_between(sysdate, u.birth_date)/12) between ac.min_age and max_age
group by
       ac.code
     , ac.age
     , ac.age_interval;

CodePudding user response：

You can rewrite the query by using CTE in order to prevent repeatedly writing FLOOR(MONTHS_BETWEEN(sysdate, u.birth_date) / 12) which and age aliased form of it will be used within the GROUP BY lists such as

WITH u0 AS
 (SELECT FLOOR(MONTHS_BETWEEN(sysdate, birth_date) / 12) AS age, COUNT(id) AS total
    FROM sec_users
   GROUP BY FLOOR(MONTHS_BETWEEN(sysdate, birth_date) / 12)),
u2 AS
 (SELECT (CASE
           WHEN age BETWEEN 13 AND 18 THEN
            'Өсвөр үе'
           WHEN age BETWEEN 19 AND 25 THEN
            'Залуу үе'
           WHEN age BETWEEN 26 AND 35 THEN
            'Идэр үе'
           WHEN age BETWEEN 36 AND 45 THEN
            'Хижээл үе'
           WHEN age BETWEEN 46 AND 60 THEN
            'Өтөл үе'
           WHEN age BETWEEN 61 AND 100 THEN
            'Өндөр үе'
           ELSE
            'other'
         END) age,
         (CASE
           WHEN age BETWEEN 13 AND 18 THEN
            'kid'
           WHEN age BETWEEN 19 AND 25 THEN
            'bigger_kid'
           WHEN age BETWEEN 26 AND 35 THEN
            'adult'
           WHEN age BETWEEN 36 AND 45 THEN
            'bigger_adult'
           WHEN age BETWEEN 46 AND 60 THEN
            'big_adult'
           WHEN age BETWEEN 61 AND 100 THEN
            'caption'
           ELSE
            'other'
         END) code,
         (CASE
           WHEN age BETWEEN 13 AND 18 THEN
            '13-18'
           WHEN age BETWEEN 19 AND 25 THEN
            '19-25'
           WHEN age BETWEEN 26 AND 35 THEN
            '26-35'
           WHEN age BETWEEN 36 AND 45 THEN
            '36-45'
           WHEN age BETWEEN 46 AND 60 THEN
            '46-60'
           WHEN age BETWEEN 61 AND 100 THEN
            '61-100'
           ELSE
            'other'
         END) age_interval,
         SUM(total) AS total
    FROM u
   GROUP BY u.age)
SELECT code,
       MAX(age) AS age_level,
       MAX(age_interval) AS age_interval,
       SUM(total) AS total
  FROM sec_survey_users su
  LEFT JOIN u
    ON su.user_id = u.id
 WHERE su.survey_id = 'D3A21B1C2D8334C9E055824F7FFC5DF4'
 GROUP BY code