Answer

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

How do I sum the values of a column based on a comparison with another column?

    Requires Free Membership to View

    Login

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

This was first published in April 2010

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top