Oracle Sql SUM MAX

Question

I have following scenario:

ID    Campus    Credit_Hr
===== ======      ====           
1      MIC          3                
1      Warrens      4            
1      Online       3             
1      Online       3  
2      MIC          5
2      Warrens      3
2      Online       6
3      Online       3
3      Online       3
3      West         2
4      Warrens      3
4      MIC          3
4      West         7
5      Online       3
5      West         3
5      East         3

Warrens and MIC are major campus. So, when Warrens and MIC has equal credit hr, like in ID 4, chose either Warrens / MIC

For ID 1: Warrens > MIC , chose Warrens though sum(Online) = 6 and is greater
For ID 2: MIC> Warrens, chose MIC
For ID 3: no Major Campus (Warrens/MIC) so chose max credit hr. er sum(online) is maximum so chose Online
For ID 5: West / East /Online all are minor campus, so chose any of them.

There are more than 50 campuses in real.

For id = 3, 'Online' appears twice. Could other campuses also appear more than once for the same id? In that case, what do you need - just the max from the credit hours column? Do you need to SUM first (grouped by id and campus)? You have SUM in the title of your question, and then you don't have any SUM anywhere in the question - please clarify. — user5683823
– user5683823, Commented Feb 11, 2019 at 16:55
Yes, you are correct. when Online Appears twice we need to Sum and the result will be 6 , we need to compare 6 with West 2 which .So, max credit hr will be 6. Same will be case with MIC and Warrens. If sum of credit hr for MIC is greater , then MIC , so basically we will go for sum first. — Sajjan Lamichhane
– Sajjan Lamichhane, Commented Feb 12, 2019 at 5:26

Ponder Stibbons · Accepted Answer · 2019-02-11 16:09:34Z

1

Assign information about MAJOR campuses, then use this column for ordering, in addition to the sum of hours:

^{dbfiddle demo}

select * 
  from (
    select a.*, row_number() over (partition by id order by major, sm desc) rn
      from (
        select id, campus, 
               case when campus in ('MIC', 'Warrens') then 1 else 2 end major, 
               sum(credit_hr) over (partition by id, campus) sm
          from t) a)
  where rn = 1

answered Feb 11, 2019 at 16:09

Ponder Stibbons

14.9k2 gold badges23 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user5683823 · Accepted Answer · 2019-02-11 17:01:42Z

If all you need is to select max credit hours for each ID, but in such a way that if credit hours exist for 'MIC' or 'Warrens' for a given ID, then all other campuses for the same ID should be ignored, then the most efficient way is to use the FIRST aggregate function, like so:

with
  sample_data(id, campus, credit_hr) as (
    select 1, 'MIC'    , 3 from dual union all
    select 1, 'Warrens', 4 from dual union all
    select 1, 'Online' , 3 from dual union all
    select 1, 'Online' , 3 from dual union all
    select 2, 'MIC'    , 5 from dual union all
    select 2, 'Warrens', 3 from dual union all
    select 2, 'Online' , 6 from dual union all
    select 3, 'Online' , 3 from dual union all
    select 3, 'Online' , 3 from dual union all
    select 3, 'West'   , 2 from dual union all
    select 4, 'Warrens', 3 from dual union all
    select 4, 'MIC'    , 3 from dual union all
    select 4, 'West'   , 7 from dual union all
    select 5, 'Online' , 3 from dual union all
    select 5, 'West'   , 3 from dual union all
    select 5, 'East'   , 3 from dual
)
select   id, 
         max(credit_hr) keep (dense_rank first 
             order by case when campus in ('MIC', 'Warrens') then 0 end)
         as max_hr
from     sample_data
group by id
order by id
;

   ID             MAX_HR
----- ------------------
    1                  4
    2                  5
    3                  3
    4                  3
    5                  3

You can also modify the query (add more columns) to show whether the max was from a main campus (that is, if that ID had ANY credit hours from one of the major campuses), and/or to show which campus had the max hours for that ID (or one of the campuses, if there was a tie for most hours).

For ID: 3, there are two Online with 3 credit hr each, sum of which will be 6 which is greater than credit hr for West, so MAX_HR should display 6. Similarly, there may be cases with MIC -3 and Warrens -2, Warrens - 2, and ONL -6 for same ID. Warrens sum of credit hour will be (2+2+2) > MIC (3) , so Warrens 6 will be Max Credit hr.

Collectives™ on Stack Overflow

Oracle Sql SUM MAX

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related