Forum Stats

• 3,733,254 Users
• 2,246,738 Discussions

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Get max of sum(col)

Member Posts: 737
edited December 2007
Hi
select col1, sum(col2) as sm
from
(huge complex query)

How can I select the max(sm) along with col1? Other than using the following method:
select col, sum(col2) as sm from (<huge query>) where
sum(col2) = (select max(sm) from <huge complex query>)

• Member Posts: 670
Give this a try (not tested)
```select col1,sm
from (select col1, sum(col2) as sm
from (huge complex query)
group by col1
order by 2 desc)
where rownum = 1```
• Member Posts: 737
edited December 2007
Hi
Thanks.
This is a good idea but wont this return a single row? I may have same values for the sum column (that is multiple rows).So I need all rows that will have that max value for sum(col2).

ID SM
1 15
2 15
3 15
4 13
5 10
Here I need 3 rows where id = 1, 2 & 3.

Thx!

Message was edited by:
b~o~s~t~o~n
• Member Posts: 379
It depends what you want.

If you want the sum of col2 for every given col1, it is a standard GROUP BY query:

SELECT col1, SUM(col2) FROM (huge complex query) GROUP BY col1;

Of course, if you are using SQL*Plus, there is a non-SQL solution using COMPUTE SUM of Col2 ON Col1

Or, a sub-query could be used, as the prior replier showed.
• Member Posts: 379

WITH Sums AS (SELECT Col1, SUM(Col2) SM FROM (huge complex query))
SELECT Col1, SM FROM Sums WHERE SM = (SELECT MAX(SM) FROM Sums);
• Member Posts: 670
I may have same values for the sum column (that is multiple rows)
I didn't consider that. Analytics will probably do the trick. I'll see if I can work up an example.
• Member Posts: 8,968 Gold Crown
edited December 2007
If I got it right
```select col1,sm
from (select col1,
sm,
rank() over (order by sm desc) r
from (select col1,
sum(col2) as sm
from (huge complex query
)
group by col1  -- as rbglossip says
)
)
where r = 1```
Regards

Etbin

Didn't see any group by in the OP but makes sense. It's up to the OP anyway.

Message was edited by: Etbin
user596003
• Member Posts: 670
I was just about to post the same query, just needs a group by on the inner query.
• Member Posts: 17
I always thought the W3 SQL School's tutorial was easy to read and understand back in the day....

It goes through many topics.....

http://www.w3schools.com/sql/default.asp
• Member Posts: 737
Thanks sooooo much all. It worked.
• Member Posts: 1,072 Bronze Badge
edited December 2007
```select col,sm
from (select col, sum(col2) as sm,
max(sum(col2)) over() as maxSm
from <huge query>
group by col)
where sm = maxSm;```
• Member Posts: 8,968 Gold Crown
nice. I like it better than mine more compact & easier to understand (rank being somehow obscure)

Regards

Etbin
• Member Posts: 1,072 Bronze Badge
We can use below solution ,too.
Maybe, below solution is more simple.
```select col,sm
from (select col, sum(col2) as sm,
rank() over(order by sum(col2) desc) as Rn
from <huge query>
group by by col)
where Rn = 1;```
• Member Posts: 737
Thanks. It works:)
• Member Posts: 8,968 Gold Crown
Definitely. Thank you. I guess I was too anxious to reply (one needs - at least at my age - to be proud of his reaction time from time to time ;-) lol). Perhaps it's time to revise my method (as dictated by the daily routine) of writing sql - wrapping subqueries just keeping the answer correct first and simplifying next (provided there is some time left to do it).

Regards

Etbin
This discussion has been closed.