Q

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

SQL expert Karen Morton explains how to use the SQL MAX function to sum the values of one column based on comparison with another in this expert tip.

How do I sum the values of a column based on a comparison with another 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

This was first published in April 2010

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close