How to use the SQL MAX function to sum the values of a column

Eg., I have columns num1, num2, val, sumval.

I want to group num1 and num2 columns and get sum(sumval), and this sum should happen for max(val) = val in the same table.

create table tempval(num1 number, num2 number, val number, sumval number);

insert into tempval values(111, 222, 1, 10); 
insert into tempval values(111, 222, 1, 20); 
insert into tempval values(111, 222, 2, 30); 
insert into tempval values(111, 222, 2, 40);
insert into tempval values(111, 333, 1, 15); 
insert into tempval values(111, 333, 4, 25);

In the above, I need to get the output as two records as like below:

A. 111, 222, 70		(70 is the sum value based on 2 records since 2 is max for this group.
B. 111, 333, 25		(25 is the sum value based on 1 record since 4 is max value for this group)

Try this:

SQL>create table tempval(num1 number, num2 number, val number, sumval number);

Table created.

SQL>
SQL>insert into tempval values(111, 222, 1, 10);

1 row created.

SQL>insert into tempval values(111, 222, 1, 20);

1 row created.

SQL>insert into tempval values(111, 222, 2, 30);

1 row created.

SQL>insert into tempval values(111, 222, 2, 40);

1 row created.

SQL>insert into tempval values(111, 333, 1, 15);

1 row created.

SQL>insert into tempval values(111, 333, 4, 25);

1 row created.

SQL>commit ;

Commit complete.

I'm executing this query first to show you how the final answer is built. I use the MAX analytic function to compute the MAX val column value for each grouping of num1, num2.

SQL>select num1, num2, val, sumval,
  2  max(val) over (partition by num1, num2) max_val
  3  from tempval ;

           NUM1            NUM2             VAL          SUMVAL         MAX_VAL
--------------- --------------- --------------- --------------- ---------------
            111             222               1              10               2
            111             222               1              20               2
            111             222               2              30               2
            111             222               2              40               2
            111             333               1              15               4
            111             333               4              25               4

6 rows selected.

Now, all I have to do is create a filter predicate that only retains rows where the VAL and MAX_VAL match and then group things up and do the final SUM.

SQL>
SQL>select num1, num2, sum(sumval) totsum
  2  from
  3  (
  4  select  num1, num2, val, sumval,
  5     max(val) over (partition by num1, num2) max_val
  6  from tempval
  7  )
  8  where val = max_val
  9  group by num1, num2
 10  ;

           NUM1            NUM2          TOTSUM
--------------- --------------- ---------------
            111             222              70
            111             333              25

2 rows selected.

Have a question for Karen Morton? Send an e-mail to editor@searchoracle.com