Oracle 12C SQL Aggregate Query Logic

Question

I need help in writing SQL query for below mixed aggregation scenario: Consider below Emp Table. Need to aggregate employee records by manager.

Aggregaton Logic: Need top Rating under manager. Eg. for Mgr 10, Rating should be Super , For 30, Good

Need EmpDesig and Dob for emp with least rating. Sal and Bonus should be aggregated by Sum().

   Mgr Emp Rating  EmpSal EmpDesig Bonus     Dob
   10  11   Avg      1000   JE        50   Jan-85
   10  12   Good     1500   SE        30   Mar-85
   10  12   Super    2000   ASE       60   Mar-84
   20  21   Good     1000   SE        40   Apr-85
   20  22   Avg      1000   AST       45   Dec-86
   30  31   Good     500    SE        80   Oct-85
   30  32   Good     1000   ASE       70   Jan-87
   30  33   Avg      1500   ASC       90   Nov-85
   30  34   Avg      500    JE       20    Jan-89

So Output for above Dataset should be

   Mgr Rating  EmpSal EmpDesig Bonus     Dob
   10   Super    4500   JE       140   Jan-85
   20   Good     2000   AST      85    Dec-86
   30   Good     3500   JE       260   Jan-89

       Create table emp(MgrID number, EmpID number, varchar2(30),EmpSal number, EmpDesig 
      varchar2(30),
      Bonus number,    Dob varchar2(30));
      insert into emp values (10,  11,   'Avg',      1000,   'JE',        50,   'Jan-85');
      insert into emp values (10,  12,   'Good',     1500,   'SE',        30,   'Mar-85');
      insert into emp values (10,  12,   'Super',    2000,   'ASE',       60,   'Mar-84');
      insert into emp values (20,  21,   'Good',     1000,   'SE',        40,   'Apr-85');
      insert into emp values (20,  22,   'Avg',      1000,   'AST',       45,   'Dec-86');
      insert into emp values (30,  31,   'Good',     500,   'SE',        80,   'Oct-85');
      insert into emp values (30,  32,   'Good',    1000,   'ASE',       70,   'Jan-87');
      insert into emp values (30,  33,   'Avg',      1500,   'ASC',       90,   'Nov-85');
      insert into emp values (30,  34,   'Avg',      500,   'ASC',       90,   'Jan-89');

I tried building logic with below query but could not.

   Select mgr, 
    max(rating) over (partition by mgr order by case Rating when 'Super' then 1 when 'Good' then 2 
    when 'Avg' then 3 end) rating,
    row_number () over (partition by mgr order by case Rating when 'Super' then 1 when 'Good' then 2 
    when 'Avg' then 3 end) rating_rnk,
    sum(empSal),
    sum(bonus),
    max(empdesig) over (partition by mgr order by case Rating when 'Super' then 3 when 'Good' then 2 
    when 'Avg' then 1 end) empdesig,
    row_number () over (partition by mgr order by case Rating when 'Super' then 3 when 'Good' then 2 
    when 'Avg' then 1 end) empdesig_rnk,

    max(dob) over (partition by mgr order by case Rating when 'Super' then 3 when 'Good' then 2 when 
    'Avg' then 1 end) dob,
     row_number () over (partition by mgr order by case Rating when 'Super' then 3 when 'Good' then 
     2 when 'Avg' then 1 end) dob_rnk
    from 
    emp
    group by mgr

Answer 1

This is conditional aggregation. I think the key is to enumerate the ratings:

SELECT mgrid,
    (
        CASE MIN(rating_order)
            WHEN 1   THEN 'Super'
            WHEN 2   THEN 'Good'
            WHEN 3   THEN 'Avg'
        END
    ) AS max_rating,
    MAX(empdesig) KEEP (dense_rank first order by rating_order desc, empid) AS empdesig,
    MAX(dob) KEEP (dense_rank first order by rating_order desc, empid) AS dob,
    SUM(empsal) AS sal, 
    SUM(bonus)
FROM (SELECT e.*, 
         (
             CASE rating
                 WHEN 'Super' THEN 1
                 WHEN 'Good' THEN 2
                 WHEN 'Avg' THEN 3 end ) as rating_order
             FROM emp e
         ) e GROUP BY mgrid;