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?
Continue Reading This Article
Enjoy this article as well as all of our content, including E-Guides, news, tips and more.
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 [email protected]